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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.