## Base SAS programming_Cumulative sum

Solved
Occasional Contributor
Posts: 12

# Base SAS programming_Cumulative sum

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?

 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........,

Accepted Solutions
Solution
‎01-11-2017 06:17 AM
Posts: 1,018

## Re: Base SAS programming_Cumulative sum

/* 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;

All Replies
Super User
Posts: 19,772

## Re: Base SAS programming_Cumulative sum

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;``````

Super User
Posts: 5,499

## Re: Base SAS programming_Cumulative sum

If I understand correctly, for each ID:

• On observations 1-12, cum_sum should be missing
• On observation 13, cum_sum should be the sum from observations 1-12
• On observation 14, cum_sum should be the sum for observations 2-13

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.

Super User
Posts: 7,766

## Re: Base SAS programming_Cumulative sum

``````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;

``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,023

## Re: Base SAS programming_Cumulative sum

``````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;``````
Solution
‎01-11-2017 06:17 AM
Posts: 1,018

## Re: Base SAS programming_Cumulative sum

/* 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;

☑ This topic is solved.