Hi,
I have a data set with 100 records for customerID 1. I need for every row cumulative sum considering only 12 rows before the current row.
PS: In which case i will not have cumulative sum for the first 12 rows.
How can I do this in SAS?
Thanks in Advance.
ID | Number | cum_sum |
1 | 1 | |
1 | 2 | |
1 | 3 | |
1 | 4 | |
1 | 5 | |
1 | 6 | |
1 | 7 | |
1 | 8 | |
1 | 9 | |
1 | 10 | |
1 | 11 | |
1 | 12 | |
1 | 13 |
So on........,
/* rolling 12-record sums*/
data want;
set have;
by id;
counter+1;
if first.id then counter=1;
_sum+number - ifn(counter>12,lag12(number),0);
if counter>=12 then cum_sum=_sum;
drop _sum;
run;
If you have SAS ETS, using PROC EXPAND is the easiest method. Otherwise:
data have;
ID=1;
do number=1 to 13;
output;
end;
run;
data want;
set have;
by id;
retain running_total counter;
if first.id then
do;
cum_sum=0;
counter=0;
end;
running_total+number;
counter+1;
if counter>12 then
cum_sum=running_total;
run;
PROC EXPAND
proc expand data=have out=want;
by id;
convert number=cum_sum / transformout=(cusum 12 trimleft 12);
run;
If I understand correctly, for each ID:
If that's the case, a DATA step can code it:
data want;
set have;
by ID;
if first.ID then do;
counter=0;
temp_sum=0;
end;
back12 = lag12(number);
counter + 1;
if counter > 12 then do;
cum_sum = temp_sum;
temp_sum = temp_sum - back12;
end;
temp_sum + number;
drop number temp_sum back12;
run;
It's untested code, and it gives me a headache just to look at it. But it ought to work. Good luck.
data want;
set have;
by id;
if first.id then counter = 1;
else counter + 1;
cum_sum = sum(
number,lag(number),lag2(number),lag3(number),lag4(number),lag5(number),
lag6(number),lag7(number),lag8(number),lag9(number),lag10(number),lag11(number)
);
if counter lt 12 then cum_sum = .;
drop counter;
run;
data have;
ID=1;
do number=1 to 13;
output;
end;
run;
data want;
set have;
array x{0:11} _temporary_;
if _n_ le 12 then cum_sum=.;
else cum_sum=sum(of x{*});
x{mod(_n_,12)}=number;
run;
/* rolling 12-record sums*/
data want;
set have;
by id;
counter+1;
if first.id then counter=1;
_sum+number - ifn(counter>12,lag12(number),0);
if counter>=12 then cum_sum=_sum;
drop _sum;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.