DATA Step, Macro, Functions and more

Base SAS programming_Cumulative sum

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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?

Thanks in Advance.

 

IDNumbercum_sum
11 
12 
13 
14 
15 
16 
17 
18 
19 
110 
111 
112 
113 

 

So on........,


Accepted Solutions
Solution
‎01-11-2017 06:17 AM
Valued Guide
Posts: 797

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;

 

 

View solution in original post


All Replies
Super User
Posts: 17,819

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

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: 6,936

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: 9,676

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
Valued Guide
Posts: 797

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 264 views
  • 7 likes
  • 6 in conversation