My question is about the conditional cumulative sum in SAS. I think it can be explained better by using sample. I have following dataset:
Date Value
01/01/2001 10
02/01/2001 20
03/01/2001 30
04/01/2001 15
05/01/2001 25
06/01/2001 35
07/01/2001 20
08/01/2001 45
09/01/2001 35
I want to find the cumulative sum of value. My condition is if cumulative sum more than 70, it should be 70 and the next cumulative sum should be began from the excessive value over 70 and so on.. More preciesly, my new data should be:
Date Value Cumulative
01/01/2001 10 10
02/01/2001 20 30
03/01/2001 30 60
04/01/2001 15 70
05/01/2001 25 30 ( 75-70=5+25=30)
06/01/2001 35 65
07/01/2001 20 70
08/01/2001 45 60 ( 85-70=15+45=60)
09/01/2001 35 95 ( because its last value)
Many thanks in advance
One simple way:
data have;
input Date :mmddyy10. Value;
format date mmddyy10.;
datalines;
01/01/2001 10
02/01/2001 20
03/01/2001 30
04/01/2001 15
05/01/2001 25
06/01/2001 35
07/01/2001 20
08/01/2001 45
09/01/2001 35
;
data want;
set have end=done;
cumulative + value;
if cumulative > 70 and not done then do;
remainder = cumulative - 70;
cumulative = 70;
output;
cumulative = remainder;
end;
else output;
drop remainder;
run;
proc print; run;
What do you have so far?
I don't know if this is you as well, but it's an almost identical question:
http://stackoverflow.com/questions/41556209/conditional-cumulative-sum-in-sas
Hi Reeza,
I have seen this post.The problem is, it is not the same post. In my case, I want to cumulative sum equal to the value that I select as my condition. And the next cumulative sum should be the sum of excessive value over cumulative sum and next value. More detailed, in that case ( as said in link, it is 10):
5 5 3 8 4 4 ( because 12>10) 3 7
But in my case it should be:
5 5 3 8 4 10 3 5 ( 12-10=2+3=5)
One simple way:
data have;
input Date :mmddyy10. Value;
format date mmddyy10.;
datalines;
01/01/2001 10
02/01/2001 20
03/01/2001 30
04/01/2001 15
05/01/2001 25
06/01/2001 35
07/01/2001 20
08/01/2001 45
09/01/2001 35
;
data want;
set have end=done;
cumulative + value;
if cumulative > 70 and not done then do;
remainder = cumulative - 70;
cumulative = 70;
output;
cumulative = remainder;
end;
else output;
drop remainder;
run;
proc print; run;
Hi.
Here's another way of doing it, just using algebra.
data want;
set have end=done;
drop _:;
_cumulative + value; * sum value;
cumulative=min(70+(done*_cumulative),_cumulative); * minimize to 70 or not if done;
_cumulative=_cumulative-int(cumulative/70)*70; * subtract if > 70;
run;
min function will return the minimum value and int will returns the integer part.
Hope it helps.
Daniel Santos @ www.cgd.pt
Many 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.