Complicated Cumulative Sum

Khaladdin — Sun, 22 Jan 2017 10:53:51 GMT

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:

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.

Re: Complicated Cumulative Sum

Shmuel — Sun, 22 Jan 2017 17:54:59 GMT

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;

topic Complicated Cumulative Sum in All Things Community

Complicated Cumulative Sum

Re: Complicated Cumulative Sum