Help using Base SAS procedures

Cumulative sum in multiple columns in SAS

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

Cumulative sum in multiple columns in SAS

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


Accepted Solutions
Solution
‎06-29-2015 04:08 PM
Super User
Posts: 10,538

Re: Cumulative sum in multiple columns in SAS

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;

View solution in original post


All Replies
Super User
Posts: 10,538

Re: Cumulative sum in multiple columns in SAS

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 varSmiley Happy;

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.

Contributor
Posts: 23

Re: Cumulative sum in multiple columns in SAS

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.

Solution
‎06-29-2015 04:08 PM
Super User
Posts: 10,538

Re: Cumulative sum in multiple columns in SAS

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;

Contributor
Posts: 23

Re: Cumulative sum in multiple columns in SAS

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

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 577 views
  • 0 likes
  • 2 in conversation