12-02-2016 10:15 AM
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 Average Sum(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 |
12-02-2016 11:52 AM - edited 12-02-2016 11:53 AM
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).
12-02-2016 10:37 AM
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.
12-02-2016 11:14 AM
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 |
12-02-2016 11:52 AM - edited 12-02-2016 11:53 AM
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).
12-02-2016 01:31 PM
Fabulous, it does work. Thank you so much, Rick_SAS.
12-02-2016 01:42 PM
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.
12-06-2016 11:22 AM
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.
12-06-2016 11:38 AM
Sure. Search for MOVSTD in the documentation for the transformation operations.
Need further help from the community? Please ask a new question.