SAS - moving statistics monthly

SAS_moving_stats_monthly

/*This program illustrates how to loop through by calendar months and calculate a moving statistic*/

data have;
set sashelp.stocks;
run;

*sort for faster processing and add index;
proc sort data=have out=have (index=(date));
by date;
run;


options mprint symbolgen;
%macro rolling_p90(ds_in = , window = , ds_out=);

*select minimum date and maximum date;
proc sql noprint;
select min(date) into :min_date
from &ds_in.;
select max(date) into :max_date
from &ds_in.;
quit;

*determine number of months between start and end date;
%let n_months = %sysfunc(intck(month, &min_date, &max_date));

%put &n_months.;
%put &window.;

*start of loop;

%do i=1 %to %eval(&n_months - &window.);

*get start of period;
%let period_start = %sysfunc(intnx(month, &min_date., %eval(&i-1), b));
%put &period_start;
*calculate end of period;
%let period_end = %sysfunc(intnx(month, &period_start., &window.-1, e));

*calculate summary statistics;
proc means data=&ds_in. noprint nway;
where date between  &period_start. and &period_end.;
var open;
output out=summary p90=p90;
run;

*add in date window for comparison;
data summary;
set summary;
period_start = &period_start;
period_end = &period_end.;
format period_: date9.;
run;

*append to main data set;
proc append base=&ds_out data=summary force;
run;

*drop table between loops;
proc sql;
drop table summary;
quit;

%end;

%mend;

%rolling_p90(ds_in = have , window = 24 , ds_out= want);