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.
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)
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;
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.