Search

Calculating returns in month t-1, month t-12 through t-2, and month t-36 through t-13
 Posted: 22 April 2015 08:41 PM [ Ignore ]
Newbie
Total Posts:  2
Joined  2015-03-09

Hi everyone,

I’m trying to replicate a paper, but I’m not sure how to code the following (in SAS):

1. stock return in month t - 1
2. cumulative stock return from month t-12 through t-2
3. cumulative stock return from month t-36 through t-13

I’ve pulled monthly return data from crsp, but I’m not quite sure how to generate these variables using SAS code. Any help would be sincerely appreciated!

Courtney

 Profile

 Posted: 23 April 2015 03:12 PM [ Ignore ]   [ # 1 ]
Total Posts:  901
Joined  2011-09-19

hi Courtney,

The following macro would append fiscal year return to a dataset. You can change the code that computes the start/end month to make it flexible (using the INTNX function). It assumes that you have crsp.msf locally (in general a good idea to have common datasets on your harddisk).

``` /*   macro that appends fiscal year raw return  dsin, required:  key, permno, datadate dsout: dataset to be created*/%macro getReturn(dsin=, dsout=);/* create return window dates: mStart - mEnd */data getr_1 (keep = key permno mStart mEnd);set &dsin; /* create variable with last day of first month of fiscal year, e.g. January 31 if year end is December 31 */mStart=INTNX('Month',datadate, -11, 'E'); mEnd=datadate;if permno ne .;  format mStart mEnd date.;run;  /* get stock return */proc sql;  create table getr_2    (keep = key permno mStart mEnd date ret) as  select a.*, b.date, b.ret  from   getr_1 a, crsp.msf b  where a.mStart <= b.date <= a.mEnd   and a.permno = b.permno  and missing(b.ret) ne 1;quit;/* force unique obs */  proc sort data = getr_2 nodup;by key date;run;/* sum ret - thanks again, Lin */proc sql; create table getr_3 as  select key, exp(sum(log(1+ret)))-1 as ret from getr_2 group by key;quit;/* create output dataset */proc sql;  create table &dsout; as  select a.*, b.ret from &dsin; a left join getr_3 b on a.key=b.key;quit;%mend;  ```

Invoke with:

``` %getReturn(dsin=work.dataIn, dsout=work.dataOut);  ```

So, dataIn should have the needed variables on them (like permno, take it from ccm linktable), and a variable called key (I typically have such a variable constructed as key = gvkey || fyear).

Hope this helps,

Joost

Signature