turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Cumulative sum in multiple columns in SAS

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

06-29-2015 09:39 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cercig

06-29-2015 04:08 PM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cercig

06-29-2015 11:35 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

06-29-2015 03:41 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to cercig

06-29-2015 04:08 PM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

06-30-2015 02:07 AM

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