topic Re: re: Cumulative Sum in SAS Programming

re: Cumulative Sum

twildone — Wed, 26 May 2021 19:15:51 GMT

Hi...I am trying to create a new variable that cumulates the total revenue. Because revenue was received on different dates for the same group1, I found the total revenue for each group and now I would like to cumulate the revenue after group1. Not sure if this the best approach.

Thanks.

data Have;
	input group1 $ date :date9. revenue;
	format date date9.;
	datalines;
A 01jan2020 100
B 01jan2020 50
A 02jan2020 120
B 02jan2020 60
A 03jan2020 80
B 03jan2020 60
A 04jan2020 50
B 04jan2020 100
;
run;
 
proc sort data=Have;
	by group1 date;
run;

proc sql noprint;
	create table Have1 as 
		select *,
		sum(revenue) as revenue1
		from work.Have
		group by group1;
quit;

data Want;
	set Have1;
	by group1 revenue1;
	retain cumulative_revenue;
	if first.revenue1 then cumulative_revenue = revenue1;
run;

Want:

group1   date      revenue  cumulative_revenue
A     01jan2020      100         350
A     02jan2020      120         350
A     03jan2020       80         350
A     04jan2020       50         350
B     01jan2020       50         620
B     02jan2020       60         620
B     03jan2020       60         620
B     04jan2020      100         620

Re: re: Cumulative Sum

Kurt_Bremser — Wed, 26 May 2021 19:55:26 GMT

Do a double DO loop:

proc sort data=have;
by group1 date;
run;

data want;
do until (last.group1);
  set have;
  by group1;
  cumulative_revenue + revenue;
end;
do until (last.group1);
  set have;
  by group1;
  output;
end;
run;

Re: re: Cumulative Sum

twildone — Wed, 26 May 2021 20:03:24 GMT

Thanks Kurt!!!. It worked.

Re: re: Cumulative Sum

jimbarbour — Wed, 26 May 2021 20:04:17 GMT

Well, if it's giving you the correct results, I'd say that's a pretty good way to do it. Now, if you need to do this on huge datasets and you encounter performance problems, then maybe the style of coding needs another look, but otherwise I think it's fine. Correct results are 95% of the deal. Making code "best" is secondary -- unless

It's difficult for others to understand
It's difficult to maintain
It's "touchy," i.e. tends to fail a lot or frequently comes up with incorrect data. You generally want robust code.
Has significant performance problems
Uses excess resources (disk hog, CPU hog, etc.) such that others are adversely affected.

Just my perspective,

Jim

Re: re: Cumulative Sum

Kurt_Bremser — Wed, 26 May 2021 20:07:56 GMT

If the code is not entirely clear to you, feel free to ask.

Re: re: Cumulative Sum

twildone — Thu, 27 May 2021 14:28:35 GMT

Hi Jim....thanks for your comments. I am actually working with a large dataset. I did try Kurt's code and it seems to have worked for at least the few records that I have checked. But if there is a much robust approach and method that can be used I would certainly would like to try it. Thanks.

Re: re: Cumulative Sum

jimbarbour — Fri, 28 May 2021 00:21:57 GMT

Kurt's code looks pretty good. Any time you are able to process a big dataset in a single pass, that's fastest way to go. If the dataset is truly big, say more than 500 million rows, the are ways to do parallel processing which can really speed things up.

If you can't get the performance you need, and you read truly large datasets on a regular basis, then I could help you set that up, but it is a pretty advanced programming technique.