Form rolling data windows

[merton76]

Hi,

We want to create rolling 52 week windows of time series data (there is one data point per week). For example, assuming that we have observations for 3 years (156 weeks), then there would be 104 rolling windows. We are able to do this for one ID, however we are unsure how to extend this to multiple IDs

The following code works for one ID and is taken from http://www.lexjansen.com/nesug/nesug12/fi/fi08.pdf and assumes, for simplicity, 3 week rolling windows. We adopted the brute force approach because our database is not extremely large.

DATA rwin / view=rwin;

ws = 3;

nwin = nrecs - ws +1;

do w=1 to nwin;

do p=w to w + ws -1;

set have point=p nobs=nrecs;

output;

end;

end;

stop;

run;

proc reg data=rwin noprint outest=stats tableout;

by w;

model y=x;

quit;

We tried inserting a first. statement in an effort to apply the program to each ID, however we have not had any luck...

Example data is below. Many Thanks!

ID	Date	Y	X
1	12/08/2005	-0.00067	0.006616
1	19/08/2005	-0.00068	0.010581
1	26/08/2005	-0.00069	0.021692
1	2/09/2005	0.074719	0.01386
1	9/09/2005	-0.00031	0.008635
1	16/09/2005	-0.00022	0.017548
1	23/09/2005	-0.00022	0.03136
1	30/09/2005	-0.00023	0.033201
1	7/10/2005	-0.00024	0.01449
1	14/10/2005	-0.00024	0.035164
1	21/10/2005	-0.00025	-0.00197
1	28/10/2005	-0.00025	0.008037
2	18/07/2003	-0.00065	-0.00199
2	25/07/2003	0.00687	-0.00158
2	1/08/2003	-0.00065	0.013269
2	8/08/2003	-0.00057	0.022922
2	15/08/2003	-0.00059	0.030418
2	22/08/2003	0.003177	-0.01567
2	29/08/2003	-0.00059	0.010914
2	5/09/2003	-0.0006	0.032219
2	12/09/2003	-0.00061	-0.04194
2	19/09/2003	-0.02793	0.055343
2	26/09/2003	-0.00884	-0.01608
2	3/10/2003	-0.00092	-0.01413
2	10/10/2003	-0.00094	-0.02378
2	17/10/2003	-0.00096	0.037424
2	24/10/2003	-0.00098	0.016513
2	31/10/2003	0.000112	0.011719
2	7/11/2003	-0.00095	-0.00938
2	14/11/2003	-0.00534	-0.0107
2	21/11/2003	-0.00102	0.005464

[Ksharp]

Make a Macro and CALL EXECUTE().

Dummy Code could like :

%reg(id=,date=);

proc reg data=have(where=(id=&id and date between &date and %evalf(&date+&ws) ))  noprint outest=stats&id&date tableout;

model y=x;

quit;

%mend reg;

%let ws=3;

proc sort data=have(keep=id date) out=temp nodupkey;by id date;run;

data _null_;

set temp;

call execute('%reg(id='||id||',date='||date||')' );

run;

Xia Keshan