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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.