rolling regression

[Amalik]

I am using the following kind of  data to run the fama french regression on my data. I am using a rolling window of 36 months.

permno	date	er	rmrf	hml	smb
1	1/31/2008
1	2/29/2008
1	uptil 12/31/2017
2	4/31/2009
2	5/31/2009
2	uptil 12/31/2017
3	1/31/2009
3	2/29/2009
3	uptil 12/31/2013
uptil 2048

Although this macro works perfectly well if I run it on just one fund but when I am running it on the whole data (2048 funds) , SAS stops working and doesn’t gives me any results. Plus when I stop the code in between to see whether it’s running the desired way, I can see that it’s actually not. This is because my data ranges from jan2008 to dec2017 but SAS starts creating rolling windows outside this range e.g feb2015 to jan2018 and so on. 

The rolling windows should actually restart from jan2008 or whatever the start year for the next permno is (as the data available varies with respect to each permno). Also, my output file (for one fund) shows me the results of the last window and not all the windows.However, I want the output file to show results for all the windows.

* Macro with parameters;

%MACRO RW (data=  ,out_ds= ,model_equation= ,permno=  , date= ,STARTd=  ,ENDd= ,regprint=);

%DO i=1 %TO 24756; /*8*12NUMBER OF 36th month rolling window */

                       /* all the dates available so 107 covers the whole period*/

 /* dm log 'clear';

        dm 'odsresults; clear';

*/

  %let Date = %Sysfunc( InputN( 31jan2008 ,Date9.)) ; /*to create a macro variable for reference date which is the beginning of the rolling window date*/

    %let dated = %Sysfunc( PutN( &date , Date9.)) ; /*to input date and give it format by putn*/

     %put Date = &Date ;

      %put Dated = &Dated;

%IF &i =1 %THEN %DO;

 %let START = %Sysfunc( InputN( 31jan2008, Date9. )) ; /*to create a macro variable for reference date which is the beginning of the rolling window date*/

    %let STARTd = %Sysfunc( PutN( &start , Date9.)) ; /*to input date by input and give it format by putn*/

     %put START = &START ;

      %put STARTd = &STARTd;

%let END= %Sysfunc( Intnx( Month , &start , 35,end )) ;

 %let  ENDd=%Sysfunc( PutN( &END , Date9.)) ;

  %put end   = &end;

  %put endd  = &endd;

%GOTO CONTINUE;/*TO SEND THE FIRST WINDOW(DATE) TO THE NEXT STEP WHICH IS SQL FOR SUBSETTING THE MAIN DATA*/

%END;

%Else %IF &i>1 %THEN %DO;

%let Start = %Sysfunc( Intnx( month, &Date ,&i-1)) ;/*to specify intervals with intnx ,start should be 1 month after the previouse starting date*/

 %let End = %Sysfunc( Intnx( Month , &start , 35,end)) ;/*monthly interval for end to specify 36 rolling window*/

  %put start = &start

        end = &end;

%let STARTd=%Sysfunc( PutN( &START , Date9.)) ; /*to get DATE9. FORMAT FOR date instead of default date*/

 %let  ENDd=%Sysfunc( PutN( &END , Date9.)) ;

  %PUT STARTd=&STARTd 

        ENDd=&ENDd;

%continue:

proc sql  NOPRINT; /* TO SUBSET OUR COMPLETE DATASET IN ACCORDANCE TO THE ROLLING WINDOWS INTERVALS*/

 create table temp1 as

 select*

 from TEST

 where DATE BETWEEN "&STARTd"d and "&ENDd"d;

 quit;

/*1-2: RUNNING multiple regression/PER INTERVALS out EACH TIME*/*********************************************;

 proc reg data=temp1 noprint outest=Out_Estimates ;

model ER = RMRF HML SMB;

run;

quit; 

title 'out_estimates, loop ' &i;

proc print data=Out_Estimates;run;

%END;

* Show INPUT data this regression: ;

title 'Temp1, loop ' &i ;

proc print data=temp1; RUN;

%END;

%MEND RW;

*Invoke the macro;

%RW (data= TEST ,

out_ds= temp1,

model_equation= ER = RMRF HML SMB,

permno= 1 , date=date ,

STARTd= 31jan2008 ,

ENDd= 31dec2017,

regprint=noprint)

; QUIT;

I am unable to identify what’s the problem in my coding. Can you please help me out in this? 

[ballardw]

First thing would be post the code in a code box opened using the forums {I} icon at the top of the message window to preserve formatting. The message windows reformat text something fierce. So your posted code is hard to read.

I don't see quite where you use PERMNO at all.

You aren't using it as a grouping variable, by variable, class variable or referenced anywhere in your macro. So that would be a major cause of not working with more than one "permno". Maybe you intended something like this to get output for each permno:

proc sql  NOPRINT; /* TO SUBSET OUR COMPLETE DATASET IN ACCORDANCE TO THE ROLLING WINDOWS INTERVALS*/
   create table temp1 as
   select*
   from TEST
   where DATE BETWEEN "&STARTd"d and "&ENDd"d
   order by Permno date;
quit;
/*1-2: RUNNING multiple regression/PER INTERVALS out EACH TIME*/*********************************************;
proc reg data=temp1 noprint outest=Out_Estimates ;
   by permno;
   model ER = RMRF HML SMB;
run;
quit; 

Or something else that looped over a list of Permno values and subset the data in the Proc sql to the desired Permno.

I am not at all clear what this means:

%DO i=1 %TO 24756; 

Since you are apparently attempting some sort of looping based on dates I would strongly suggest using a data set where you have more flexible loops and use CALL EXECUTE to generate the statements for proc sql, proc reg and proc print.

[bstarr]

Not a whole lot of info about your data. It looks like you have monthly returns? In either case, your problem likely lies with this:

%DO i=1 %TO 24756;

Why 24756? Then it gets used here:

%let Start = %Sysfunc( Intnx( month, &Date ,&i-1)) 

At the end of the loop, your start date is March 31, 4072. If you have stock returns for the future, please let me know 🙂

I'd recommend making it more data driven/automated. How are you actually running this? Do you have a list of 2048+ %RW macro calls? You can automate that using your table fairly easily.

As for saving estimates from each proc reg, you need some sort of dataset to accumulate the results. Simple version:

data allreg;
set allreg Out_Estimates (in=a);
    if a then model = &i;
run;

Look into proc append if that starts to take too long.

[Amalik]

You are right, I am actually confused on how to treat the loops. I have 2048 funds in hand, the data is monthly but for some funds the data is available for the entire period but for the others for some of the years. However, for each fund I have at-least 5 years of consecutive data. e.g for fund fund date range will be jan-08 to Dec-17 but for other it might be Apr-10 to Mar-15 that is, the date may vary from one fund to the next.

I have an idea that there is something wrong with 24756 but how should I treat the loops then?

[bstarr]

How are your data organized? Your best option would be to create a driver dataset that contains all your necessary information - essentially the same as the table in your first post. If you don't have this, you can derive it from your analytic dataset - I assume all your fund information is in one dataset?

Also, are you running 3 different rolling regs per fund as indicated in your initial table? It is unclear what the requirements are. If you are running rolling regs, there shouldn't be any need to have multiple iterations per fund since you would be duplicating effort. To that end, I would suggest obtaining the min and max dates for each fund, then use that as inputs to your macro and run each fund only once for the entire time frame you have data. Once you have that, you will need to loop through your data, likely having two loops. The first loop is for each fund (e.g. i=1 to 2048). Then, the second loop is for the rolling regs themselves (e.g. j=36 to &end) where &end is the number of data points derived from your table created above. In your input dataset, you can create some counter variable to drive the rolling regs rather than dates where the first data point for each fund is 1. If your data are structured in a long format with all funds stacked on top of each other, it could be as simple as:

data test2;
set test;
retain id;
if first.permno then id=1;
else id+1;
run;

Then your proc reg might look like:

proc reg data=test noprint outest=out_estimates;
model ER = RMRF HML SMB;
where permno = &i and (&j-35 <= ID <= &j);
run;