I'm trying to do a moving average with PROC EXPAND where the average takes into account only those observations that occur within the previous seven calendar days, which is not necessarily the previous seven observations. I have a dataset that has some gaps, such as weekends, holidays, etc., where the data is not reported. But I don't want to average observations that are outside the weekly window. In other words, if I'm calculating the moving average on a Monday, I want the average to be only those observations from the previous Tuesday to the current day, regardless of whether there are observations for the weekend.
Currently PROC EXPAND is taking seven previous observations, which can average in numbers from a few days before that. Here's my code:
proc expand DATA=gas.gasprices OUT=gas.GasPrices_ma METHOD=none;
ID Date;
convert spread=Spread_MA / TRANSFORMOUT = (nomiss movave 7 trim 6);
run;
... View more