12-01-2017 03:34 AM
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.
12-01-2017 03:46 AM - edited 12-01-2017 04:59 AM
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)
12-01-2017 08:14 AM
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;
12-01-2017 08:42 AM
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.
12-01-2017 09:21 AM
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; data want (drop=leading_value); merge have have (firstobs=3 keep=sales rename=(sales=leading_value)); smoothed_average=ifn(_n_>=3,mean(lag2(sales),lag(sales),sales,lag(leading_value),leading_value),.); if leading_value=. then smoothed_average=.; run;
This program assumes there are no missing values for sales, which means "if leading_value=. then smoothed_average=.;" statement will prevent unwanted averages at the end of the data set.