topic Re: Base SAS programming_Cumulative sum in SAS Programming

Base SAS programming_Cumulative sum

Haritha1 — Fri, 16 Dec 2016 05:43:45 GMT

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

Re: Base SAS programming_Cumulative sum

Reeza — Fri, 16 Dec 2016 06:19:06 GMT

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;

Re: Base SAS programming_Cumulative sum

Astounding — Fri, 16 Dec 2016 08:15:03 GMT

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.

Re: Base SAS programming_Cumulative sum

Kurt_Bremser — Fri, 16 Dec 2016 10:14:16 GMT

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;

Re: Base SAS programming_Cumulative sum

Ksharp — Sat, 17 Dec 2016 07:00:06 GMT

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;

Re: Base SAS programming_Cumulative sum

mkeintz — Sun, 18 Dec 2016 01:38:21 GMT

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