Solved
Contributor
Posts: 33

# Cumulative sum by three groups

Hello,

I want to calculate cumulative sum by three variables. The following data give some ideas about what I want:

 ISIN Fyear Group Values Want AAA 2010 1 10 10 AAA 2010 2 11 21 AAA 2010 3 12 33 AAA 2011 1 15 15 AAA 2011 2 18 33 AAA 2011 3 19 52 BBB 2010 1 20 20 BBB 2010 2 30 50 BBB 2010 3 40 90 BBB 2011 1 10 10 BBB 2011 2 15 25 BBB 2011 3 20 45

Basically, I want a variable "want"  which is the cumulative sum of "values" by ISIN, Fyear and group. I found some SAS codes from SAS community, but those codes are used to calculate the cumulative sum by one or two variables. I tried to modify and test those codes, but they did not work correctly. I think I need a new code. I wonder if anyone can help me a little bit. Thank you very much for your help!

Cheers, Thierry.

Accepted Solutions
Solution
‎07-07-2017 06:43 AM
Super User
Posts: 6,176

## Re: Cumulative sum by three groups

[ Edited ]

If you examine the results you are seeking, you are actually getting a cumulative sum by 2 variables, not 3.  GROUP has nothing to do with the logic.  So assuming your data set is already sorted (making the BY statement legal):

data want;

set have;

by isin fyear;

if first.fyear then want=values;

else want + values;

run;

All Replies
Solution
‎07-07-2017 06:43 AM
Super User
Posts: 6,176

## Re: Cumulative sum by three groups

[ Edited ]

If you examine the results you are seeking, you are actually getting a cumulative sum by 2 variables, not 3.  GROUP has nothing to do with the logic.  So assuming your data set is already sorted (making the BY statement legal):

data want;

set have;

by isin fyear;

if first.fyear then want=values;

else want + values;

run;

Contributor
Posts: 33