Community talk about communities.sas.com. Meta!

Complicated Cumulative Sum

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Complicated Cumulative Sum

 

I have a tricky question about conditional sum in SAS. Actually, it is very complicated for me and therefore, I cannot explain it by words. Therefore I want to show an example:

 A      B
 5      3
 7      2
 8      6
 6      4
 9      5
 8      2
 3      1
 4      3

As you can see, I have a datasheet that has two columns. First of all, I calculated the conditional cumulative sum of column A ( I can do it by myself-So no need help for that step):

 A      B    CA
 5      3    5
 7      2    12
 8      6    18    
 6      4    8     ((12+8)-18)+6
 9      5    17   
 8      2    18
 3      1    10    (((17+8)-18)+3
 4      3    14    

So my condition value is 18. If the cumulative more than 18, then it equal 18 and next value if sum of the first value after 18 and exceeds amount over 18. ( As I said I can do it by myself ) So the tricky part is I have to calculate the cumulative sum of column B according to column A:

 A      B    CA    CB
 5      3    5     3
 7      2    12    5
 8      6    18    9.5 (5+(6*((18-12)/8)))
 6      4    8     5.5 ((5+6)-9.5)+4
 9      5    17    10.5 (5.5+5)
 8      2    18    10.75 (10.5+(2*((18-7)/8)))
 3      1    10    2.75 ((10.5+2)-10.75)+1
 4      3    14    5.75 (2.75+3)

As you can see from example the cumulative sum of column B is very specific. When column A is equal our condition value (18), then we calculate the proportion of the last value for getting our condition value (18) and then use this proportion for computing cumulative sum of column B.


Accepted Solutions
Solution
‎01-23-2017 04:07 AM
Trusted Advisor
Posts: 1,586

Re: Complicated Cumulative Sum

Posted in reply to Khaladdin

The core of the solution is using RETAIN:

 

Here is a tested code. If you are satisfied unmark the KEEP statement .

data have;
 input a b;
datalines;
5 3
7 2
8 6
6 4
9 5
8 2
3 1
4 3
; run;
data want; set have; retain ca cb lag_a prev_ca alt_cb 0 prop 1 flag 0; lag_a = lag(a); if flag = 0 then do; prev_ca = ca; if ca + a > 18 then ca=18; else ca = ca+a; if ca ge 18 then do; alt_cb = cb + b; cb=cb+b*(18-prev_ca)/a; prop = cb; flag = 1; end; else cb = cb + b; end; else do; /* flag=1 */ ca= lag_a; cb = alt_cb - prop + b; flag = 0; end; *keep a b ca cb ; /* unmark if satisfied with results */ run;

View solution in original post


All Replies
Solution
‎01-23-2017 04:07 AM
Trusted Advisor
Posts: 1,586

Re: Complicated Cumulative Sum

Posted in reply to Khaladdin

The core of the solution is using RETAIN:

 

Here is a tested code. If you are satisfied unmark the KEEP statement .

data have;
 input a b;
datalines;
5 3
7 2
8 6
6 4
9 5
8 2
3 1
4 3
; run;
data want; set have; retain ca cb lag_a prev_ca alt_cb 0 prop 1 flag 0; lag_a = lag(a); if flag = 0 then do; prev_ca = ca; if ca + a > 18 then ca=18; else ca = ca+a; if ca ge 18 then do; alt_cb = cb + b; cb=cb+b*(18-prev_ca)/a; prop = cb; flag = 1; end; else cb = cb + b; end; else do; /* flag=1 */ ca= lag_a; cb = alt_cb - prop + b; flag = 0; end; *keep a b ca cb ; /* unmark if satisfied with results */ run;
☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 242 views
  • 1 like
  • 2 in conversation