03-14-2016 12:47 PM
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?
03-14-2016 12:54 PM
Please post sample data and expected output.
Please define how are you calculating 3 year return - you had monthly data previously.
03-14-2016 01:27 PM
Good points - thanks. In terms of sample data, here it is:
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.
03-14-2016 09:41 PM
Yeah. if an individual obs represent one year, you can combine LAG skill and MERGE skill together . Like :
have(firstobs=2 keep=CUSIP Annret rename=(CUSIP=CUSIP1 Annret=Annret1))
have(firstobs=3 keep=CUSIP Annret rename=(CUSIP=CUSIP2 Annret=Annret2));
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);