Form rolling data windows

Reply
Occasional Contributor
Posts: 7

Form rolling data windows

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!

IDDateYX
112/08/2005-0.000670.006616
119/08/2005-0.000680.010581
126/08/2005-0.000690.021692
12/09/20050.0747190.01386
19/09/2005-0.000310.008635
116/09/2005-0.000220.017548
123/09/2005-0.000220.03136
130/09/2005-0.000230.033201
17/10/2005-0.000240.01449
114/10/2005-0.000240.035164
121/10/2005-0.00025-0.00197
128/10/2005-0.000250.008037
218/07/2003-0.00065-0.00199
225/07/20030.00687-0.00158
21/08/2003-0.000650.013269
28/08/2003-0.000570.022922
215/08/2003-0.000590.030418
222/08/20030.003177-0.01567
229/08/2003-0.000590.010914
25/09/2003-0.00060.032219
212/09/2003-0.00061-0.04194
219/09/2003-0.027930.055343
226/09/2003-0.00884-0.01608
23/10/2003-0.00092-0.01413
210/10/2003-0.00094-0.02378
217/10/2003-0.000960.037424
224/10/2003-0.000980.016513
231/10/20030.0001120.011719
27/11/2003-0.00095-0.00938
214/11/2003-0.00534-0.0107
221/11/2003-0.001020.005464
Super User
Posts: 10,023

Re: Form rolling data windows

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

Ask a Question
Discussion stats
  • 1 reply
  • 152 views
  • 0 likes
  • 2 in conversation