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?
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
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;
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.
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.