SAS Data Step offers last, first, lag, retain function/statement to allow users to process the data vertically. Many users tend to get stuck in this programming paradigm when using SAS to process the data. They normally ignore most time they can simplify the processing using horizontally. The key to use this paradigm is to use PROC TRANSPOSE to transform the data and then use a Data Step to process it. Here is an example.We have a list of customers visiting the stores and we want to know the highest purchase amount in last three visits.
proc sort data=customers;
by customer_id descending visit_date;
run;
data customers;
set customers;
by customer;
retain visit_cnt max_amount;
if first.customer then do;
max_amount = 0;
visit_cnt = 1;
end;
if visit_cnt <= 3 & amount > max_amount then max_amt = amount;
visit_cnt = visti_cnt + 1;
run;
proc sort data=customers;
by customer_id descending visit_date;
run;
proc transpose data=customer prefix=amt;
by customer;
var amount;
run;
data customers;
set customers;
max_amt = max(of amt1 - amt3);
run;
Here is another post which demonstrates the use of this programming paradigm.
