If you're close to my age, you probably remember Joe Walsh's song Ordinary Average Guy. That song popped into my head when I was writing this example about moving averages, so I encourage you to listen to it while reading the blog post.

Many people are looking at graphs lately - graphs of things like the number of disease cases per day. And with real world data, the data points often have a bit of variability, and therefore an average line is often added to the graph, to make it easier to see the trend. Here's a recent example from the BBC website:

The line in the graph is labeled as the seven-day average ... but which seven days do they use? This is what they call a moving (or rolling) average, therefore at each point along the graph, seven days 'close' to that point are used to calculate the average for that point in time. In the graph above, I suspect that for each point in time, they are using the average of the current day and the previous 6 days (later, you'll see why I suspect this!)

How can you create such a graph in SAS? One way is to use Proc Expand (which is part of the SAS/ETS product). The code below calculates the moving average (movave) for some hypothetical data using a 7-day window. The 7 days used in the average consist of the current day, and the 6 days prior. I also use an option to trim the 6 days on the left side of the graph, since they wouldn't have 7 data points to include in the average.

proc expand data=my_data out=my_data;
convert cases=cases_left_smoothed / method=none transformout=(movave 7 trimleft 6);
run;

I then use the following code to plot the data points as 'needles' (basically, skinny bars), and overlay the moving average as a red line:

proc sgplot data=my_data noborder;
needle y=cases x=date /
   lineattrs=(color=orange thickness=2px)
   markers markerattrs=(color=gray77 symbol=circle);
series y=cases_left_smoothed x=date / lineattrs=(color=red);
yaxis display=(nolabel noline noticks)
   grid gridattrs=(pattern=dot color=gray88)
   valueattrs=(color=gray33 size=10pt);
xaxis display=(nolabel) type=time
   valueattrs=(color=gray33 size=10pt);
run;

Nice graph, eh? ... Well, maybe if you squint your eyes a little when you look at it. But if you're a discerning user, you might notice that the red average line doesn't really represent the data points (at the top of the bars) very well. This is because we're using the left average. With the left average, when the data values are increasing, the average (red) line will tend to be below the data values. And when the data values are decreasing, the average (red) line will tend to be above the data values. (This is why I suspect the BBC used the left average.)

Rather than using the left average for this type of data, I prefer to use the center average. This uses the current day, and the 3 prior days, and the 3 following days, as the 7 days for the average. This makes the average line 'fit' the data points much better, for this type of data, imho. Here's the code to calculate the centered average (and trim the 3 days on the left of the graph, and 3 days on the right side of the graph):

proc expand data=my_data out=my_data;
convert cases=cases_center_smoothed / method=none transformout=(cmovave 7 trim 3);
run;

There! - Now doesn't that blue line follow the data points (the circles a the tops of the little bars) much better!? Hopefully you can put this tip to use, and improve your own graphs. Here's a link to my code & data, if you'd like to experiment with it.

Feel free to discuss in the comments, and for fun feel free to post a link to any "average line" graph you find in the news recently, that you think uses the "left" moving average rather than the "centered" moving average.