SAS_ Conditional Cumulative sum

[Khaladdin]

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

[Reeza]

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

[Khaladdin]

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)     

[PGStats · Accepted solution]

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;

[DanielSantos]

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

[Khaladdin]

Many thanks.