DATA Step, Macro, Functions and more

calculate rolling window beta

Reply
Occasional Contributor
Posts: 11

calculate rolling window beta

Hello all 

I am having some trouble in calculating rolling window beta by using the previous 48 month data.  I have multiple stocks (20000 stock) and for each CUSIP at each date I want to obtain betai and betam using the following regression :

Rt = alpha + betaiRti + betamRtm +e

 I have tried the following sas script, but my problem is that not all cusip have the same observations some have less then 48 month date. Any help would be much appreciated

data nstockregfinal4  / view= nstockregfinal4;
do grp = 0 to nrecs-48;

     do j = 1 + grp to 48 + grp;
          set nstockregfinal3 nobs=nrecs point=j;
          output;
          end;
     end;
stop;
run;

proc reg data=nstockregfinal4  outest=Coeff noprint;
    by grp;
    model Rt = Rtm Rti;
    output out=Res p=predicted r=residus;
run;
quit;

*however this sas code works for just one cusip code so I have tried the following code but i am not sure it s right;


datanstockregfinal5  / view= nstockregfinal5;
 array _X1 {48} _temporary_ ;
 array _X2 {48} _temporary_ ;
 array _Y {48} _temporary_ ;
 set nstockregfinal3;
 by  CUSIP;
 retain N 0;
 N = ifn(first.CUSIP,1,N+1);
 I=mod(N-1,48)+1;
 _X1{I}=Rtm;
 _X2{I}=Rti;
 _Y{I}=Rt;
 if N>=48 then do I= 1 to 48;
 Rtm=_X1{I};
 Rti=_X2{I};
 Rt=_Y{I};
 output;
 end;
run;
proc reg data=nstockregfinal5  noprint outest=myests;
 by CUSIP REPORT_DATE;
 model Rt = Rtm Rti;
 run;
 quit;
Super User
Posts: 9,673

Re: calculate rolling window beta

Make a macore or

Post it at IML forum if you want IML solution .

SAS Super FREQ
Posts: 3,475

Re: calculate rolling window beta

This is essentially the problem treated in Chapter 4 (p 33-39) of Using SAS in Financial Research. If you can find a copy in a library or friend's bookshelf, you can read the chapter. If not, you still might be able to make sense of the SAS code, which is available for free from the book's web site (click "Example Code and Data").

Super User
Posts: 9,673

Re: calculate rolling window beta

[ Edited ]

Fixed some error.

 

data have;
call streaminit(1234);
 do id=1 to 4;
  do date='01jan2010'd to '01jan2014'd;
   Rt=rand('normal');
   Rtm=rand('lognor');
   Rti=rand('uniform');
   output;
  end;
 end;
 format date date9.;
run;


proc freq data=have noprint;
table id*date/out=key nocum nopercent;
run;

data key;
 set key(drop=count percent);
 start=intnx('month',date,-48,'s');
 end=date;
 drop date;
 format start end date9.;
run;


%macro reg(id=,start=,end=);
proc reg data=have(where=(id=&id and date between &start and &end))
 outest=Coeff noprint;
    model Rt = Rtm Rti;
run;
proc append base=want data=coeff force;run;
%mend;

proc delete data=want;run;
data _null_;
 set key;
 call execute(cats('%reg(id=',id,',start=',start,',end=',end,')'));
run;

 

 

Ask a Question
Discussion stats
  • 3 replies
  • 187 views
  • 0 likes
  • 3 in conversation