topic Re: Cumulative sum by three groups in SAS Data Management

Cumulative sum by three groups

tritringuyen — Fri, 07 Jul 2017 09:48:49 GMT

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.

Re: Cumulative sum by three groups

Astounding — Fri, 07 Jul 2017 10:23:14 GMT

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;

Re: Cumulative sum by three groups

tritringuyen — Fri, 07 Jul 2017 10:43:26 GMT

Thank you very much! It is very useful! 🙂