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
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;
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;
Thanks Kurt!!!. It worked.
If the code is not entirely clear to you, feel free to ask.
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
Just my perspective,
Jim
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.