Your logic for last 4 weeks index appears incorrect or exactly the same as days. Here's one way to do it, in a single data step. A bit cumbersome but straightforward enough: Data Have; input Date mmddyy10. Cust_Num$ Amount Days Index ; cards; 02-03-2015 ABC 150 7 2 01-27-2015 ABC 100 5 2 01-20-2015 ABC 100 5 2 01-13-2015 ABC 100 5 2 01-06-2015 ABC 100 5 2 02-03-2015 DEF 150 5 3 01-27-2015 DEF 150 6 3 01-20-2015 DEF 150 6 3 01-06-2015 DEF 100 6 3 run; proc sort data=have; by cust_num descending date; run; data want; set have; by cust_num descending date; retain date_out amount_out days_out index_out amount_prev days_prev index_prev running_days running_index; if first.cust_num then do; call missing (date_out, amount_out, days_out, index_out, amount_prev, days_prev, index_prev, running_days, running_index); date_out=date; amount_out=amount; days_out=days; index_out=index; end; else do; amount_prev=sum(amount_prev, amount); running_days=sum(amount*days,running_days); running_index=sum(index*days, running_index); end; if last.cust_num then do; days_prev=running_days/amount_prev; index_prev=running_index/amount_prev; output; end; drop running: date amount days index; run;
... View more