BookmarkSubscribeRSS Feed
bera00
Obsidian | Level 7

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;
3 REPLIES 3
Ksharp
Super User

Make a macore or

Post it at IML forum if you want IML solution .

Rick_SAS
SAS Super FREQ

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").

Ksharp
Super User

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;

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1847 views
  • 0 likes
  • 3 in conversation