Computing moving average in SAS

Question

I'm trying to use SAS to compute a moving average for x number of periods that uses forecasted values in the calculation. For example if I have a data set with ten observations for a variable, and I wanted to do a 3-month moving average. The first forecast value should be an average of the last 3 observations, and the second forecast value should be an average of the last two observations, and the first forecast value.

Answer 1

If you have for example data like this:

data input;
infile datalines;
length product $10 period value 8;
informat period yymmdd10.;
format period yymmdd10.;
input product $ period value;
datalines;
car 2016-01-01 10
car 2015-12-01 20
car 2015-11-01 30
car 2015-10-01 40
car 2015-09-01 30
car 2015-08-01 15
;
run;

You can left join input table itself with a condition:

input t1 left join input t2
    on t1.product = t2.product
    and t2.period between intnx('month',t1.period,-2,'b') and t1.period
    group by t1.product, t1.period, t1.value

With this you have t1.value as current value and avg(t2.value) as 3 months avg. To compute 2 months avg change every value that is older then previos period to missing value with ifn() function:

avg(ifn( t2.period >= intnx('month',t1.period,-1,'b'),t2.value,. ))

Full code could looks like this:

proc sql;
    create table want as
        select t1.product, t1.period, t1.value as currentValue,
            ifn(count(t2.period)>1,avg(ifn( t2.period >= intnx('month',t1.period,-1,'b'),t2.value,. )),.) as twoMonthsAVG,
            ifn(count(t2.period)>2,avg(t2.value),.) as threeMonthsAVG
        from input t1 left join input t2
            on t1.product = t2.product
            and t2.period between intnx('month',t1.period,-2,'b') and t1.period
        group by t1.product, t1.period, t1.value
    ;
quit;

I've also added count(t2.perion) condition to return missing values if I haven't got enough records to compute measure. My result set looks like this: