Calculation of Cumulative 3-year returns based on annual return data

[mig7126]

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 compoundin​g 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! 

[Reeza]

Please post sample data and expected output.

Please define how are you calculating 3 year return - you had monthly data previously. 

[mig7126]

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.

[Ksharp]

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;