Moving Weighted Standard Deviation using Proc Expand

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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?

YYYYMMCountWeight (W)W*V

Weighted      Average

Sum(W*V)

Weighted Standard Deviation 
20130311,5540.0128148.128 904.1793075
20130411,9570.0256306.59482.030807
20130511,9330.0385458.962493.5225834
20130610,7420.0513550.87259590.57907
20130711,1450.0641714.42329186.5279
20130812,0070.0769923.6152695.447154
20130910,8030.0897969.592735.58869
20131012,5760.10261,289.8558673.72743
20131110,3840.11541,198.15237892.9696
20131212,1120.12821,552.8210947.32214
20140112,6880.1411,789.33106286.6415
20140212,4640.15381,917.5463829.98501
20140313,754  11819.78724056.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).

View solution in original post


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.  

 

Capture.PNG

 

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 405 views
  • 3 likes
  • 2 in conversation