rollingreg

Reply
Contributor
Posts: 53

rollingreg

Hi,

 

I have the data in following form (its actually from 1/31/2008 to 12/31/2017).

 

permnodateersmbhmlrmrf
11/31/20081-6.4-4.50.21
12/29/2008-23.52.90.13
13/31/20082.55.63.1-17
21/31/20083-6.4-4.50.21
22/29/20081.463.52.90.13
23/31/2008-0.4565.63.1-17

 

I want to run a rollingreg on this data and I am using the following code;

 

/*Rolling Window for er*/
/*consider past 36 excess returns for each subsequent month*/

%let ws=36;
DATA tmp1.er_rw;
array _er {&ws} _temporary_ ;
array _DATE {&ws} _temporary_ ;
array _SMB {&ws} _temporary_ ;
array _HML {&ws} _temporary_ ;
set tmp1.finaluse;
by PERMNO;
retain N 0 winID 0;
N = ifn(first.PERMNO,1,N+1); 
I=mod(N-1,&ws)+1;
_er{I}=er;
_DATE{I}=DATE;
if N>=&ws then 
do;winID = winID+1;
do I= 1 to &ws;
er=_er{I};

month=_DATE{I};
output;
end; 
end;
format month mmddyy9.;
drop N I;
run;
proc sort data=tmp1.er_rw;
by winid;
run;

However, the output that I am getting is in this form;

permnodateersmbhmlrmrfmonthwinID
112/31/20101-6.4-4.50.211/31/20081
112/31/2010-2-6.42.90.132/29/20081
112/31/20102.5-6.43.1-173/31/20081
212/31/20103-6.4-4.50.211/31/20082
212/31/20101.463.52.90.132/29/20082
212/31/2010-0.4565.63.1-173/31/2008

2

 

 

I want the output to look like this instead;

permnodateersmbhmlrmrfmonthwinID
112/31/20101-6.4-4.50.211/31/20081
11/31/2011-2-6.42.90.132/29/20081
12/29/20112.5-6.43.1-173/31/20081

 

Can someone please let me know where I am making mistake?

Contributor
Posts: 53

Re: rollingreg

I believe there is a mistake because although the month variable is rolling while the date variable is not rolling, I want both of the variables to roll

Esteemed Advisor
Posts: 5,524

Re: rollingreg

You could start with this:

 

%let ws=36;

data rw_temp;
set finaluse;
do i = 0 to &ws.;
    finalDate = intnx("month", date, i, "end");
    output;
   end;
format finalDate mmddyy10.;
drop i;
run;

proc sql;
create table rw as
select *
from rw_temp
group by permno, finalDate
having count(*) > &ws.
order by permno, finalDate, date;
quit;
PG
Ask a Question
Discussion stats
  • 2 replies
  • 107 views
  • 0 likes
  • 2 in conversation