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
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;
Please provide some sample data we can use.
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.