turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Calculation of Cumulative 3-year returns based on ...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-14-2016 12:47 PM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mig7126

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

03-14-2016 01:27 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mig7126

03-14-2016 09:41 PM

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;