Search

Calculating return for multiple firms
 Posted: 14 October 2014 12:03 PM [ Ignore ]
Newbie
Total Posts:  7
Joined  2014-10-14

Hi,

I am trying to calculate the monthly returns based on a data sample that contains: CUSIP, datadate, monthly closing price. There are multiple CUSIPs (i.e. multiple firms) and of course multiple rows of datadate and price per CUSIP. The output should be two new columns that contain the return and the cumulative return so far, for example:

CUSIP           DATADATE           PRCCM           Return                                 Cumulative return
00077R         30SEP1997         27.6250         0             0
00077R         31OCT1997         23.5000         (23.500/27.6250)-1           (23.500/27.6250)-1
00077R         30NOV1997         24.0000         (24.000/23.5000)-1           [(23.500/27.6250)-1]+[(24.000/23.5000)-1]
* same for next CUSIP / firm etc.

My - extremely simple - example in code looks like this:

``` data have;do until (targetCUSIP = last.targetCUSIP);  cumulative_return = 0;  do until (datadate = last.datadate);  return = (datadate/lag(datadate))-1;  cumulative_return = cumulative_return + return;  end; end;run;  ```

I think I need something like “DISTINCT” in the first “do until”, however am not sure how to do this.

Any help is much appreciated!

 Profile

 Posted: 14 October 2014 01:05 PM [ Ignore ]   [ # 1 ]
Total Posts:  901
Joined  2011-09-19

hi Art,

``` data art2;set art1;retain prev_prccm;/* remembers this across the rows */by cusip; /* repeat for each firm */if first.cusip then prev_prccm = .;return = prccm / prev_prccm; /* will be missing for first month */prev_prccm = prccm; /* remember for next month */run;  ```

The ‘by cusip’ is a key ingredient here: it will repeat the code-block for each cusip, so that you don’t need ‘bookkeeping’ to check if cusip changes. Obviously, the dataset needs sorting by cusip and datadate, and adjusting for stock splits.

An alternative way that I quite like is proc sql:

``` proc sql;  create table art2 as select a.*, a.prccm / b.prccm as return   from art1 a, art1 b  where a.datadate -35 <= b.datadate <= a.datadate - 28 and a.cusip = b.cusip;quit;  ```

This matches the input dataset against itself (both ‘a’ and ‘b’ are the same), and will take all records in a (a.*), and will compute the return based on b.prccm and a.prccm. The datadate range filter is to make sure ‘b’ is the prior month. (please doublecheck because it is easy to make mistakes, for example by including b.datadate as datadate_prev and inspect if it is indeed the prior month.)

best regards,

Joost

Signature

 Profile

 Posted: 14 October 2014 01:59 PM [ Ignore ]   [ # 2 ]
Newbie
Total Posts:  7
Joined  2014-10-14

Thank you Joost, very helpful! I have got the return calculation working in both codes, however I am now stuck with a little addition I made to calculate the cumulative return. The cumReturn column just remains empty and I have no idea why. If I set

``` cumReturn = return + prev_cumReturn;  ```
to
``` cumReturn = return;  ```

, I do get a column identical to return, however the below code leaves a blank column without any error messages for cumReturn.

It must be very simple, what am I missing?

``` data WIP.Returns (drop = prev_prccm prev_cumReturn);set WIP.SDC_CS_CRSP_WV;retain prev_cumReturn prev_prccm;by targetCUSIP; if first.targetCUSIP then  do; prev_prccm = .;    prev_cumReturn = .;    end;return = (prccm / prev_prccm)-1; cumReturn = return + prev_cumReturn;prev_prccm = prccm; prev_cumReturn = cumReturn;run;  ```
 Profile

 Posted: 14 October 2014 03:26 PM [ Ignore ]   [ # 3 ]
Newbie
Total Posts:  7
Joined  2014-10-14

Fixed the above by using sum() instead of +. I believe it had to do with the fact that SAS propagates missing values in arithmetic expression, which sum() does not.

 Profile

 Posted: 14 October 2014 04:10 PM [ Ignore ]   [ # 4 ]
Total Posts:  901
Joined  2011-09-19

hi Art,

Curious indeed…

I would set cum_ret to 0 instead of missing. Like this:

``` data art2;set art1;retain prev_prccm cum_return;/* remembers this across the rows */by cusip; /* repeat for each firm */if first.cusip then prev_prccm = .;if first.cusip then cum_return = 0;return = prccm / prev_prccm; /* will be missing for first month */cum_return = cum_return + return;prev_prccm = prccm; /* remember for next month */run;  ```

best regards,

Joost

Signature

 Profile

 Posted: 14 October 2014 04:43 PM [ Ignore ]   [ # 5 ]
Newbie
Total Posts:  7
Joined  2014-10-14

Hi Joost,

It becomes more curious: I tried that as well when problem solving, yet it didn’t fix it…

All sorted now though, thanks again for the quick responses!

 Profile

 Posted: 14 October 2014 04:49 PM [ Ignore ]   [ # 6 ]
Total Posts:  901
Joined  2011-09-19

Joost

Signature

 Profile