Calculate a modified version of moving averages

Reply
Regular Learner
Posts: 1

Calculate a modified version of moving averages

I have a data set which looks like this.

1.PNG

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.

2.PNG

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 ]
Posted in reply to pakalu_papito

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

Posted in reply to pakalu_papito

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

Posted in reply to pakalu_papito

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.

Trusted Advisor
Posts: 1,345

Re: Calculate a modified version of moving averages

Posted in reply to pakalu_papito

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.

Ask a Question
Discussion stats
  • 4 replies
  • 314 views
  • 6 likes
  • 5 in conversation