DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 14

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

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! 

Super User
Posts: 19,822

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

Please post sample data and expected output.

 

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

Occasional Contributor
Posts: 14

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

Good points - thanks.  In terms of sample data, here it is:

 

 

CUSIPDATEAnnret
00016510198406290.045
0001651019850628-0.08964
0003541019840629-0.24608
0003541019850628-0.32526
0003611019800630-0.11823
00036110198106300.366891
0003611019820630-0.37803
00036110198306301.203394
00036110198406290.338852
0003611019850628-0.02172
00037010198006300.057132
00037010198106301.928082
0003701019820630-0.41945
0005731019850628-0.4663
00077110198506280.184763
0007741019800630-0.19
0007741019810630-0.05556
0007741019820630-0.69907
0007741019830630-0.06667
0007741019840629-0.21429
00077P10198406290.316407
00077P10198506280.031313
0007811019820630-0.21633
00078110198306300.333333
00078110198406290.281444
00078110198506280.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.

 

 

 

Super User
Posts: 10,041

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

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;

Ask a Question
Discussion stats
  • 3 replies
  • 256 views
  • 0 likes
  • 3 in conversation