DATA Step, Macro, Functions and more

SAS_ Conditional Cumulative sum

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

SAS_ Conditional Cumulative sum

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


Accepted Solutions
Solution
‎01-12-2017 05:32 AM
Respected Advisor
Posts: 4,930

Re: SAS_ Conditional Cumulative sum

[ Edited ]
Posted in reply to Khaladdin

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;
PG

View solution in original post


All Replies
Super User
Posts: 19,851

Re: SAS_ Conditional Cumulative sum

Posted in reply to Khaladdin

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

 

Contributor
Posts: 53

Re: SAS_ Conditional Cumulative sum

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)     

Solution
‎01-12-2017 05:32 AM
Respected Advisor
Posts: 4,930

Re: SAS_ Conditional Cumulative sum

[ Edited ]
Posted in reply to Khaladdin

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;
PG
Super Contributor
Posts: 474

Re: SAS_ Conditional Cumulative sum

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

Contributor
Posts: 53

Re: SAS_ Conditional Cumulative sum

Many thanks.

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 253 views
  • 2 likes
  • 4 in conversation