Cumulative sum in multiple columns in SAS

Question

I have been searching the solution a while, but I couldn't find any similar question in SAS in communities. So here is my question: I have a big SAS table: let's say with 2 classes and 26 variables:

A B Var1 Var2 ... Var25 Var26
-----------------------------
1 1 10 20 ... 35 30
1 2 12 24 ... 32 45
1 3 20 23 ... 24 68
2 1 13 29 ... 22 57
2 2 32 43 ... 33 65
2 3 11 76 ... 32 45
...................
...................

I need to calculate the cumulative sum of the all 26 variables through the Class=B, which means that for A=1, it will accumulate through B=1,2,3; and for A=2 it will accumulate through B=1,2,3. The resulting table will be like:

A B Cum1 Cum2 ... Cum25 Cum26
-----------------------------
1 1 10 20 ... 35 30
1 2 22 44 ... 67 75
1 3 40 67 ... 91 143
2 1 13 29 ... 22 57
2 2 45 72 ... 55 121
2 3 56 148 .. 87 166
...................
...................

I can choose the hard way, like describing each of 26 variables in a loop, and then I can find the cumulative sums through B. But I want to find a more practical solution for this without describing all the variables.

On one of the websites was suggested a solution like this:

proc sort data= (drop=percent cum_pct rename=(count=demand cum_freq=cal));
weight var1;
run;

I am not sure if there is any option like "Weight" in Proc Sort, but if it works then I thought that maybe I can modify it by putting numeric instead of Var1, then the Proc Sort process can do the process for all the numerical values :

proc sort data= (drop=percent cum_pct rename=(count=demand cum_freq=cal));
weight _numerical_;
run;

Any ideas?

Can you add a sample of desired output given the input data you've posted? I'm not sure I understand your goal. — DWal
@DWal I just edited my question. An extra explanation and my goal (resulting table) — user3714330

Chris J Chris J · Accepted Answer · 2015-06-29T14:23:24

One way to accomplish this is to use 2 'parallel' arrays, one for your input values and another for the cumulative values.

%LET N = 26 ;

data cum ;
  set have ;
  by A B ;

  array v{*} var1-var&N ;
  array c{*] cum1-cum&N ;
  retain c . ;

  if first.A then call missing(of c{*}) ; /* reset on new values of A */

  do i = 1 to &N ;
    c{i} + v{i} ;
  end ;

  drop i ;
run ;

Cumulative sum in multiple columns in SAS

1 Answers