BookmarkSubscribeRSS Feed
mig7126
Obsidian | Level 7

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! 

3 REPLIES 3
Reeza
Super User

Please post sample data and expected output.

 

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

mig7126
Obsidian | Level 7

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.

 

 

 

Ksharp
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1615 views
  • 0 likes
  • 3 in conversation