Rolling beta estimation with Macro

[aj_goodnews]

Dear all,

I'm working on Fama Macbeth regression with rolling beta. My regression starts from 1970/1/1 to 2013/12/31. Each month, I run a cross sectional regression of individual stock returns on their individual betas. Betas on each regression are estimated using previous 36 month (3 years) data. For example, on 1970/1 regression, betas for each stock are estimated using data range from 1967/1/1 to 1970/1/1. on 1970/2 regression, betas for each stock are estimated using data range from 1967/2/1 to 1970/2/1, and so on. I use macro to this job. Following some text book suggestion, here is my Macro:

%macro estim;

%do x=36 % to 686;

    data temp;

      set gooddata;

  if &x -35 <= n<= &x;

  per=&x;

    proc reg data=temp noprint outest=results;

  model TRT1M=rm;  /*TRT1M is the stock return and rm is the market reutrn*/

  by gvkey per;

  quit;

  proc append base=betas1 data=results;

%end;

%mend estim;

where 686 is the maximum number of monthly observation I have in my sample.  However, not every stock have 686 month-observations, ranging form 40 to 686. My question is, if I fix my %do up to 686, SAS will only return returns for those stock with 686 observations and delete all other stocks, which is not what I want. I would like to have every stocks, no matter how many observations I have in the sample, to be reported in 'results'. How should modify this macro to achieve that. Thank you!

Logan

[art297]

Since no one has responded, I'll ask the questions that I think you need to answer.

Does a file only contain data for justone stock?

If not, what does the file really look like?

Do you have to account for missing data within a range of time that you do have stock data for?

[aj_goodnews]

Thank you for reply.

The file contains more than 500 stocks.

The structure of my dataset similar to following:

gvkey     data_date      TRT1M       rm

where gvkey is firm ID, data_date is in yyyymm format, TRT1M is stock return and rm is market return. data_date range from 1965/01 to 2013/01, but I need to calculate rolling beta from 1970/01. The only restriction I need for individual stock is that it must have sufficient data, in this case 36 moth, to estimate rolling beta each month start from 1970/01. for example, if one stock has only 37 month return data, then this stock will appear for one month when I calculate betas for that month. Each month the composition of stocks will be different and I'm fine with it.

[art297]

Logan,

I'm not sure I understand what you are trying to do, and the following code ISN'T tested, but I think that it either does or comes close to what you want to accomplish. Let the forum know if it does what you want and someone likely can propose a more efficient solution:

proc sort data=gooddata;

  by gvkey data_date;

run;

data need (drop=_:);

  do until (last.gvkey);

    set gooddata;

    by gvkey;

    array before_months(686);

    retain months:;

    if first.gvkey then do;

      call missing(of before_months(*));

    end;

    _month=intck('month', '01dec1969'd, data_date);

    _low=max(_month-36+1,1);

    do _j=_low to _month+&span.-1;

      if _month le _j then before_months(_j)=sum(before_months(_j),1);

    end;

  end;

  do until (last.gvkey);

    set gooddata;

    by gvkey;

    output;

  end;

run;

%macro estim;

  %do x=36 %to 686;

    data temp (keep=gvkey data_date TRT1M rm);

      set need;

      by gvkey;

      array before_month(*) before_months1-before_months686;

      _month=intck('month', '01dec1969'd, data_date);

      if _month ge &x.-35 and before_month(&x.) ge 36;

    run;

    proc reg data=temp noprint outest=results;

      model TRT1M=rm;

      by gvkey;

    run;

    proc append base=betas1 data=results;

    run;

  %end;

%mend estim;

%estim

[aj_goodnews]

Hello Arthur,

Thanks for your help. I'm now testing the code and it has following error message I could not figure out. Can you please help?

data need (drop=_:);

65     do until (last.gvkey);

66       set gooddata;

67       by gvkey;

68       array before_months(686);

69       retain months:;

70       if first.gvkey then do;

71         call missing(of before_months(*));

72       end;

73       _month=intck('month', '01dec1969'd, DATADATE);

74       _low=max(_month-36+1,1);

75       do _j=_low to _month + span.-1;

                                -----

                                22

                                201

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

              a numeric constant, a datetime constant, a missing value, arrayname, (, +, -, INPUT,

              NOT, PUT, ^, _NEW_, ~.

ERROR 201-322: The option is not recognized and will be ignored.

76         if _month le _j then before_months(_j)=sum(before_months(_j),1);

77       end;

78     end;

79     do until (last.gvkey);

80       set gooddata;

81       by gvkey;

82       output;

83     end;

84   run;

[art297]

Logan,

Two lines before the error you changed data_date to datadate. Your example showed that the field was called data_date, which is what I used in my code. People will be more likely to be able to address your question if you post the entire set of code that you ran, along with enough example data to test it.

Art