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 |
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.