Moving Weighted Standard Deviation using Proc Expand

Solved
Occasional Contributor
Posts: 10

Moving Weighted Standard Deviation using Proc Expand

Hi Everyone, I used the code as follow to get a 6-month moving weighted average and 6-month moving weighted standard deviation.

proc expand DATA = Input OUT = Eventdir.Mov;

convert Count = Weight_Avg/ TRANSFORMOUT = (movave (1 2 3 4 5 6));

convert Count = Weight_std / TRANSFORMOUT = (movstd (1 2 3 4 5 6));

convert Count = STD / TRANSFORMOUT = (movstd 6);

run;

Result:

But seems the weighted moving standard deviation differs a lot from non-weithed moving standard deviation and was not the result I want. My Expected Weighted Average should be as show below: 11819.78; Weighted Standard Deviation should be 850.915136.  Any one knows how to get my expected value?

 YYYYMM Count Weight (W) W*V Weighted      AverageSum(W*V) Weighted Standard Deviation 201303 11,554 0.0128 148.128 904.1793075 201304 11,957 0.0256 306.59 482.030807 201305 11,933 0.0385 458.962 493.5225834 201306 10,742 0.0513 550.872 59590.57907 201307 11,145 0.0641 714.423 29186.5279 201308 12,007 0.0769 923.615 2695.447154 201309 10,803 0.0897 969.5 92735.58869 201310 12,576 0.1026 1,289.85 58673.72743 201311 10,384 0.1154 1,198.15 237892.9696 201312 12,112 0.1282 1,552.82 10947.32214 201401 12,688 0.141 1,789.33 106286.6415 201402 12,464 0.1538 1,917.54 63829.98501 201403 13,754 11819.78 724056.5687 850.915136

Accepted Solutions
Solution
‎12-06-2016 11:18 AM
SAS Super FREQ
Posts: 3,752

Re: Moving Weighted Standard Deviation using Proc Expand

[ Edited ]

The Details section of the PROC ETS doc gives the formulas that SAS uses. If the sum of the trailing weights is unity, then the SAS formula is similar to the formula you are using, except that your formula has an extra sqrt(N) factor.  The value reported by PROC EXPAND for W_STD[12] is 245.64, which when multiplied by sqrt(12) becomes 850.92, which is the value that you are computing.

So you just have to figure out why the formulas differ by an extra factor of sqrt(N).

All Replies
SAS Super FREQ
Posts: 3,752

Re: Moving Weighted Standard Deviation using Proc Expand

It sounds like you expect to get one number for the weighted average and one number for the weighted standard deviation.

The moving weighted average will return a statistic for each time point, based on a weighted average of the previous time points.  In your data, you have a Weight column, but I don't understand where that comes from: the weights change at every time point.

I suggest you look at the following articles:

There is also a more advanced article "Rolling statistics in SAS/IML" that shows how to reproduce the numbers that come out of PROC EXPAND.

Occasional Contributor
Posts: 10

Re: Moving Weighted Standard Deviation using Proc Expand

Thank you a lot for your reply. I acutually read one of the article you shared to me yesterday, and it was really hepful.

Sorry, I should of specifying that I hope to get the 12th moving weighted Averge = 11819, the 12th moving weithed standared deviation = 851. The code below gave me expected weithed moving averge but moving weithed std not.

proc expand DATA = Input OUT = Mov;

convert Count = W_Avg / TRANSFORMOUT = (movave (1 2 3 4 5 6 7 8 9 10 11 12 ));

convert Count = W_STD / TRANSFORMOUT = (movstd (1 2 3 4 5 6 7 8 9 10 11 12));

run;

The following picture the theoritical formular I should use for calulartion.

The expected 12th moving weithted average and 12th STD are as following:

 Count Weight (W) W*V Sum(W*V) LDWStdv 11,554 0.0128 148.128 Calculation skipped 11,957 0.0256 306.590 11,933 0.0385 458.962 10,742 0.0513 550.872 11,145 0.0641 714.423 12,007 0.0769 923.615 10,803 0.0897 969.500 12,576 0.1026 1,289.846 10,384 0.1154 1,198.154 12,112 0.1282 1,552.821 12,688 0.1410 1,789.333 12,464 0.1538 1,917.538 11819.78 851
Solution
‎12-06-2016 11:18 AM
SAS Super FREQ
Posts: 3,752

Re: Moving Weighted Standard Deviation using Proc Expand

[ Edited ]

The Details section of the PROC ETS doc gives the formulas that SAS uses. If the sum of the trailing weights is unity, then the SAS formula is similar to the formula you are using, except that your formula has an extra sqrt(N) factor.  The value reported by PROC EXPAND for W_STD[12] is 245.64, which when multiplied by sqrt(12) becomes 850.92, which is the value that you are computing.

So you just have to figure out why the formulas differ by an extra factor of sqrt(N).

Occasional Contributor
Posts: 10

Re: Moving Weighted Standard Deviation using Proc Expand

Fabulous, it does work. Thank you so much, .

SAS Super FREQ
Posts: 3,752

Re: Moving Weighted Standard Deviation using Proc Expand

You are welcome. I will add that there are many different definitions for weighted statistics (see some at the Wikipedia article) because it depends what the weights represent (frequencies, inverse precision, sampling weights,...) and how they are being used. The formula you show is for the case where the w's represent frequencies of observations, but SAS treats weights differently from frequencies.  Even in SAS procedures (like MEANS or UNIVARIATE) there is a choice. The procedures support a DF= option in which you can specify the denominator for weighted statistics.

Occasional Contributor
Posts: 10

Re: Moving Weighted Standard Deviation using Proc Expand

Hi Rick_SAS, May I ask what's the defualt formular for the moving standarded deviation when I use the movstd option as below?

proc expand DATA = Input OUT = Mov;

convert Count = std / TRANSFORMOUT = (movstd 6);

run;

Thank you.

SAS Super FREQ
Posts: 3,752

Re: Moving Weighted Standard Deviation using Proc Expand

☑ This topic is solved.