topic Cumulative sum in multiple columns in SAS in SAS Procedures

Cumulative sum in multiple columns in SAS

cercig — Mon, 29 Jun 2015 13:39:41 GMT

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

...................

On one of the websites, one guy wrote a solution like:

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 idea? Thanks in advance

Message was edited by: Gurol Cerci

Re: Cumulative sum in multiple columns in SAS

ballardw — Mon, 29 Jun 2015 15:35:30 GMT

It really helps to show what the expected output is supposed to be for your given example data.
I am not sure what you mean by " through the class="B"."

Do mean to sum variables Var1 through Var26? IF those are the actual names in a data set:

sumvar = sum(of var1-var26); or sumvar = sum(of var:);

If they have a common "stem" in the name such as "sales" then

Proc Sort will not modify the data set in anyway resembling creating summaries. And there is no weight statement available with Proc Sort.

Re: Cumulative sum in multiple columns in SAS

cercig — Mon, 29 Jun 2015 19:41:07 GMT

Hi @Ballardw I updated the question. As writtern, when A=1 then it will find the accumulated sum of all 26 variables through all B values. and when A=2 then it will find the accumulated sum of all 26 variables through the B values.

Re: Cumulative sum in multiple columns in SAS

ballardw — Mon, 29 Jun 2015 20:08:03 GMT

data want;

set have;

retain cum1-cum26 .;

array cum cum1-cum26;

array var var1-var26;

by a b;

if first.a then do I= 1 to 26; cum=var;end;

else do I= 1 to 26; cum = cum+var;end;

drop var; ;

run;

Re: Cumulative sum in multiple columns in SAS

cercig — Tue, 30 Jun 2015 06:07:20 GMT

Thanks @Ballardw it was a very nice solution...