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
Rank
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! smile

Courtney

Profile
 
 
Posted: 23 April 2015 03:12 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
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.dateb.ret
  from   getr_1 a
crsp.msf b
  where a
.mStart <= b.date <= a.mEnd 
  
and a.permno b.permno
  
and missing(b.retne 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 keyexp(sum(log(1+ret)))-as ret
 from getr_2 group by key
;
quit;

/* create output dataset */
proc sql;
  
create table &dsout; as 
 
select a.*, b.ret
 from 
&dsina left join getr_3 b on a.key=b.key;
quit;
%
mend

Invoke with:

%getReturn(dsin=work.dataIndsout=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 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile