Hello,
I am new to this message board, and I appreciate the help provided on my first question that I posted earlier (How to calculate annual compounding returns based on monthly return data starting with June month). Now that I have the annual return data by stock and date (July to June annual intervals), I want to do the following:
- Calculate 3-year cumulative returns by stock and date - specifically, I want to calculate the 3-year returns for the prior 3 years (label it as Formation Return) and also the 3-year returns for the next 3 years (label it as Evaluation Return).
Therefore, each stock and date combination will have a Formation Return and an Evaluation Return.
Can someone please show me how me the SAS code, but WITHOUT using PROC SQL?
Thanks again!
Please post sample data and expected output.
Please define how are you calculating 3 year return - you had monthly data previously.
Good points - thanks. In terms of sample data, here it is:
CUSIP | DATE | Annret |
00016510 | 19840629 | 0.045 |
00016510 | 19850628 | -0.08964 |
00035410 | 19840629 | -0.24608 |
00035410 | 19850628 | -0.32526 |
00036110 | 19800630 | -0.11823 |
00036110 | 19810630 | 0.366891 |
00036110 | 19820630 | -0.37803 |
00036110 | 19830630 | 1.203394 |
00036110 | 19840629 | 0.338852 |
00036110 | 19850628 | -0.02172 |
00037010 | 19800630 | 0.057132 |
00037010 | 19810630 | 1.928082 |
00037010 | 19820630 | -0.41945 |
00057310 | 19850628 | -0.4663 |
00077110 | 19850628 | 0.184763 |
00077410 | 19800630 | -0.19 |
00077410 | 19810630 | -0.05556 |
00077410 | 19820630 | -0.69907 |
00077410 | 19830630 | -0.06667 |
00077410 | 19840629 | -0.21429 |
00077P10 | 19840629 | 0.316407 |
00077P10 | 19850628 | 0.031313 |
00078110 | 19820630 | -0.21633 |
00078110 | 19830630 | 0.333333 |
00078110 | 19840629 | 0.281444 |
00078110 | 19850628 | 0.533266 |
So, for each CUSIP and date, I want to see the following - just an example:
CUSIP DATE ANNRET FORMRET EVALRET
00016510 19840629 0.045 0.045 -0.0486
FORMRET = the cumulative return for the last 3 years; therefore, it would have to use a lag function? If there is no full data for last 3 years, then just provide the cumulative return for the number of years available. In the example above, since there was no prior annual data than 1984029 for CUSIP 00016510, the FORMRET is equal to the ANNRET.
EVALRET = the cumulative return fo the next 3 years. The cumulative returns are calculated by ((1+Annret1)*(1+Annret2)*(1+Annret3))-1. If there is no full data for the next 3 years, then just provide the cumulative return for the number of years available.
Hopefully, this additional information helps.
Thank you again and look forward to hearing from you.
Yeah. if an individual obs represent one year, you can combine LAG skill and MERGE skill together . Like :
data want;
merge have
have(firstobs=2 keep=CUSIP Annret rename=(CUSIP=CUSIP1 Annret=Annret1))
have(firstobs=3 keep=CUSIP Annret rename=(CUSIP=CUSIP2 Annret=Annret2));
lag1=lag(Annret);
lag2=lag2(Annret);
if CUSIP=lag(CUSIP) and CUSIP=lag2(CUSIP) then FORMRET=sum(Annret,lag1,lag2);
if CUSIP=CUSIP1 and CUSIP=CUSIP2 then EVALRET=sum(Annret,Annret1,Annret2);
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.