🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## How to calculate the cumulative sum by a group over the previous months

Hello,

I would like to calculate the cumulative return on the stock over the previous 11 months starting 2 months ago, i.e., the cumulative return from month t-12 to month t-2. The following data give some ideas about what I want:

 STOCK Year Month Return WANT A 2010 1 10 . A 2010 2 15 . A 2010 3 20 . A 2010 4 30 . A 2010 5 25 . A 2010 6 20 . A 2010 7 40 . A 2010 8 15 . A 2010 9 20 . A 2010 10 10 . A 2010 11 10 . A 2010 12 20 . A 2011 1 5 215 A 2011 2 10 225 A 2011 3 15 215 A 2011 4 15 205 A 2011 5 20 190 A 2011 6 25 180 A 2011 7 30 180 A 2011 8 20 165 A 2011 9 10 180 A 2011 10 10 180 A 2011 11 20 180 A 2011 12 10 180 B 2010 1 90 . B 2010 2 40 . B 2010 3 100 . B 2010 4 50 . B 2010 5 80 . B 2010 6 20 . B 2010 7 150 . B 2010 8 30 . B 2010 9 80 . B 2010 10 20 . B 2010 11 10 . B 2010 12 60 . B 2011 1 20 670 B 2011 2 15 640 B 2011 3 20 620 B 2011 4 30 535 B 2011 5 10 505 B 2011 6 70 455 B 2011 7 60 445 B 2011 8 120 365 B 2011 9 80 395 B 2011 10 100 435 B 2011 11 80 495 B 2011 12 20 585

I want a variable "WANT" which is the cumulative sum of "Return" by STOCK. For example, the cumulative return of stock A year 2011 month 1 = the cumulative return from the year 2010 month 1 to the year 2010 month 11.

I found some SAS codes from the SAS community, but those codes are used to calculate the cumulative sum by the groups. I tried to modify and test those codes, but they did not work correctly. I wonder if anyone can help me a little bit. Thank you very much for your help!

Cheers,

fongdo

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: How to calculate the cumulative sum by a group over the previous months

or, if you want missing values:

``````data WANT;
set HAVE;
WANT = ifn( lag12(STOCK)^=STOCK, .,
lag2(RETURN)+lag3(RETURN)+lag4(RETURN)+lag5(RETURN)+lag6(RETURN)+lag7(RETURN)
+lag8(RETURN)+lag9(RETURN)+lag10(RETURN)+lag11(RETURN)+lag12(RETURN) );
run;``````

4 REPLIES 4
Tourmaline | Level 20

## Re: How to calculate the cumulative sum by a group over the previous months

Please provide some sample data we can use.

Tourmaline | Level 20

## Re: How to calculate the cumulative sum by a group over the previous months

Something like this should work:

``````data WANT;
set HAVE;
WANT = (lag12(STOCK)=STOCK)
* (lag2(RETURN)+lag3(RETURN)+lag4(RETURN)+lag5(RETURN)+lag6(RETURN)+lag7(RETURN)+
lag8(RETURN)+lag9(RETURN)+lag10(RETURN)+lag11(RETURN)+lag12(RETURN) );
run;``````

Tourmaline | Level 20

## Re: How to calculate the cumulative sum by a group over the previous months

or, if you want missing values:

``````data WANT;
set HAVE;
WANT = ifn( lag12(STOCK)^=STOCK, .,
lag2(RETURN)+lag3(RETURN)+lag4(RETURN)+lag5(RETURN)+lag6(RETURN)+lag7(RETURN)
+lag8(RETURN)+lag9(RETURN)+lag10(RETURN)+lag11(RETURN)+lag12(RETURN) );
run;``````

Obsidian | Level 7

## Re: How to calculate the cumulative sum by a group over the previous months

Many thanks @ChrisNZ. Your code works well.
Discussion stats
• 4 replies
• 757 views
• 3 likes
• 2 in conversation