Regular Learner
Posts: 1

# Calculate a modified version of moving averages

I have a data set which looks like this.

I want to calculate for every row, starting from row number three a moving average with window 5. For every row i, the window will have rows i-2, i-1, i, i+1, i+2. The desired output is like this.

So for week 3, The smoothed_average is a mean of the highlighted cells.

PROC Star
Posts: 1,286

## Re: Calculate a modified version of moving averages

[ Edited ]

Use the CONVERT statement and the transformout=(cmovave 5) option in PROC EXPAND and do something like this

``````proc expand data=YourData;
id Week;
convert value=smoothed_average / transformout=(cmovave 5);
run;``````

This creates a Centered Moving Average of five observations (The two preceding, the present and the two next observations)

Super User
Posts: 10,787

## Re: Calculate a modified version of moving averages

Next time write some code to make it as a table ,not picture. No one would like to type it for you ,if you want someone answer your question.

``````data have;
input week sales promo;
cards;
1 792 0
2 271 0
3 490 0
4 1095 0
5 1439 0
6 597 0
7 588 0
8 7078 0
;
run;
proc sql;
select * ,(select avg(sales) from have where week between a.week-2 and a.week+2) as avg
from have as a;
quit;``````
New Contributor
Posts: 3

## Re: Calculate a modified version of moving averages

If u don't have SAS/ETS you won't be able to use proc expand. Here is a data step approach that should work.

``````data want;
set have nobs=nobs;
if _N_ > 2 and _N_ <= nobs-2 then do; *Logic to only calculate a smoothed average when there are two prior rows as well as two following rows;
smoothed_average = Sales;
*Grab the preceding 2 and following 2 rows;
do i = -2, -1, 1, 2;
GetPoint = _N_ + i;
set have(keep=Sales Rename=(Sales=PriorSales)) point=GetPoint;
smoothed_average + PriorSales;
end;
smoothed_average = smoothed_average / 5;
end; else
smoothed_average = .;
drop i PriorSales;
run;``````

The third line may need to change depending on your actual data to start and stop calculating properly.

Posts: 1,345

## Re: Calculate a modified version of moving averages

You want a size 5 window centered on the current observation.

PROC EXPAND is the way to go, assuming you have the sas/ets product. But if you don't, then this program will do:

``````data have;
input week sales promo;
cards;
1 792 0
2 271 0
3 490 0
4 1095 0
5 1439 0
6 597 0
7 588 0
8 7078 0
9 612 0
10 555 0
run;
merge have