SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Cumulative sum by three groups

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

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:

 

ISINFyearGroupValuesWant
AAA201011010
AAA201021121
AAA201031233
AAA201111515
AAA201121833
AAA201131952
BBB201012020
BBB201023050
BBB201034090
BBB201111010
BBB201121525
BBB201132045

 

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: 5,076

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;

View solution in original post


All Replies
Solution
‎07-07-2017 06:43 AM
Super User
Posts: 5,076

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

Re: Cumulative sum by three groups

Thank you very much! It is very useful! Smiley Happy
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 267 views
  • 1 like
  • 2 in conversation