Form rolling data windows

merton76 — Tue, 30 Jun 2015 05:55:08 GMT

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;

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

Re: Form rolling data windows

Ksharp — Tue, 30 Jun 2015 13:57:03 GMT

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

topic Form rolling data windows in SAS Programming

Form rolling data windows

Re: Form rolling data windows