rollingreg

[Amalik]

Hi,

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

permno	date	er	smb	hml	rmrf
1	1/31/2008	1	-6.4	-4.5	0.21
1	2/29/2008	-2	3.5	2.9	0.13
1	3/31/2008	2.5	5.6	3.1	-17
2	1/31/2008	3	-6.4	-4.5	0.21
2	2/29/2008	1.46	3.5	2.9	0.13
2	3/31/2008	-0.456	5.6	3.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;

permno	date	er	smb	hml	rmrf	month	winID
1	12/31/2010	1	-6.4	-4.5	0.21	1/31/2008	1
1	12/31/2010	-2	-6.4	2.9	0.13	2/29/2008	1
1	12/31/2010	2.5	-6.4	3.1	-17	3/31/2008	1
2	12/31/2010	3	-6.4	-4.5	0.21	1/31/2008	2
2	12/31/2010	1.46	3.5	2.9	0.13	2/29/2008	2
2	12/31/2010	-0.456	5.6	3.1	-17	3/31/2008	2

I want the output to look like this instead;

permno	date	er	smb	hml	rmrf	month	winID
1	12/31/2010	1	-6.4	-4.5	0.21	1/31/2008	1
1	1/31/2011	-2	-6.4	2.9	0.13	2/29/2008	1
1	2/29/2011	2.5	-6.4	3.1	-17	3/31/2008	1

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

[Amalik]

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

[PGStats]

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;