Base SAS programming_Cumulative sum

[Haritha1]

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

[Reeza]

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;

[Astounding]

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.

[Kurt_Bremser]

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;

[Ksharp]

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;

[mkeintz · Accepted solution]

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