Date: Thu, 19 Apr 2007 11:48:36 -0400
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject: Re: calculating rolling standard deviation
On Thu, 19 Apr 2007 07:52:41 -0700, PBilin <pbilin@GMAIL.COM> wrote:
>One more question. I am trying to adjust the proposed code to
>calculate rolling standard deviation for the last 3 observations
>only.
Then it actually is what I consider a *rolling* calc. Before it was a
*cumulative* calc.
Do you have SAS/ETS? PROC EXPAND might make this a lot easier.
>
>Here is the adjusted code:
>
>
>data Result(drop=SumOfX SumOfX2 xCount);
> retain SumOfX SumOfX2 xCount sumofx_12 sumofx2_12 xcount_12
>xcount_new;
> set my_data;
> by observation_no;
> if first.observation_no then do;
> xCount=1;
> SumOfX=p;
> SumOfX2=p**2;
> end;
> else do;
> xCount=xCount+1;
> SumOfX=SumOfX+p;
> SumOfX2=SumOfX2+p**2;
> end;
>q=sumofx;
>w=sumofx2;
>e=xcount;
>sumofx_12=lag3q);
>sumofx2_12=lag3(w);
>sums=q-sumofx_12;
>sums2=w-sumofx2_12;
>xcount_12=lag3(e);
>xcount_new=xcount-xcount_12;
>PopulationStdDev=((SumOfX2-SumOfX**2/xCount)/xCount)**0.5;
>
>PopulationStdDev_2=((Sums2-Sums**2/xcount_new)/xcount_new)**0.5;
>
>run;
>
>The idea is to take a difference between sumofx for current
>observation and for observation 3 periods before. Unfortunatelly, the
>code is not working properly.
>
>Would appreciate your comments!
>
>Paul
>
>On Apr 19, 10:50 am, PBilin <pbi...@gmail.com> wrote:
>> Thank you all for your help! The solutions work very neatly!
>>
>> Paul
>> On Apr 18, 10:17 pm, Huang...@PRINCIPAL.COM ("Huang, JS") wrote:
>>
>> > Dan:
>>
>> > It is a neat way to calculate sum of square of deviation from the mean
>> > iteratively. For those who are interested in the derivation here is the
>> > proof.
>>
>> > In the following mu_n denote mean of the first n terms and mu_(n-1)
>> > mean of the first n-1 terms.
>>
>> > Sum{(X_i - mu_n)**2, i=1 to n} - Sum{(X_i - mu_(n-1))**2, i=1 to n-1}
>> > = Sum{(X_i - mu_n)**2, i=1 to n-1} + (X_n - mu_n)**2 - Sum{(X_i -
>> > mu_(n-1))**2, i=1 to n-1}
>> > = Sum{((X_i - mu_(n-1)) + (mu_(n-1) - mu_n))**2, i=1 to n-1} + (X_n -
>> > mu_n)**2 - Sum{(X_i - mu_(n-1))**2, i=1 to n-1}
>> > = Sum{(X_i - mu_(n-1))**2 + 2*(X_i - mu_(n-1))*(mu_(n-1) -
>> > mu_n)+(mu_(n-1) - mu_n)**2, i=1 to n-1} + (X_n - mu_n)**2 - Sum{(X_i -
>> > mu_(n-1))**2, i=1 to n-1}
>> > = Sum{(X_i - mu_(n-1))**2, i=1 to n-1} + 2*(mu_(n-1) - mu_n)*Sum{(X_i -
>> > mu_(n-1)), i=1 to n-1} + (n-1)*(mu_(n-1) - mu_n)**2 + (X_n - mu_n)**2 -
>> > Sum{(X_i - mu_(n-1))**2, i=1 to n-1}
>>
>> > The first and last terms cancel each other and Sum{(X_i - mu_(n-1)),
>> > i=1 to n-1} = 0 and hence the above can be simplified to:
>> > (n-1)*(mu_(n-1) - mu_n)**2 + (X_n - mu_n)**2
>> > = (n-1)*(mu_(n-1) - mu_n)*(mu_(n-1) - mu_n) + (X_n - mu_n)**2
>> > = -(X_n - mu_n)*(mu_(n-1) - mu_n) + (X_n - mu_n)**2
>> > (See (***) below for derivation.)
>> > = (X_n - mu_n)*(-(mu_(n-1) - mu_n) + (X_n - mu_n))
>> > = (X_n - mu_n)*(X_n - mu_(n-1))
>> > = (X_n - mu_(n-1))*(X_n - mu_n)
>>
>> > We obtained
>> > Sum{(X_i - mu_n)**2, i=1 to n} - Sum{(X_i - mu_(n-1))**2, i=1 to
>> > n-1} = (X_n - mu_(n-1))*(X_n - mu_n)
>> > Or
>> > Sum{(X_i - mu_n)**2, i=1 to n} = Sum{(X_i - mu_(n-1))**2, i=1 to
>> > n-1} + (X_n - mu_(n-1))*(X_n - mu_n)
>>
>> > This is what is used in Dan's statement
>>
>> > v + (value_x - prior_mean) * (value_x - mean);
>>
>> > QED.
>>
>> > (***)
>> > (n-1)*(mu_n-1) - mu_n)
>> > = (n-1)*mu_(n-1) - n*mu_n + mu_n
>> > = Sum{X_i, i=1, n-1} - Sum{X_i, i=1, n} + mu_n
>> > = -X_n + mu_n
>> > = -(X_n - mu_n)
>>
>> > Replacing the first
>>
>> > -----Original Message-----
>> > From: SAS(r) Discussion [mailto:S...@LISTSERV.UGA.EDU] On Behalf Of
>>
>> > Nordlund, Dan (DSHS/RDA)
>> > Sent: Wednesday, April 18, 2007 3:12 PM
>> > To: S...@LISTSERV.UGA.EDU
>> > Subject: Re: calculating rolling standard deviation
>>
>> > > -----Original Message-----
>> > > From: SAS(r) Discussion [mailto:S...@LISTSERV.UGA.EDU] On Behalf Of
>> > > PBilin
>> > > Sent: Wednesday, April 18, 2007 10:45 AM
>> > > To: S...@LISTSERV.UGA.EDU
>> > > Subject: calculating rolling standard deviation
>>
>> > > hello,
>>
>> > > I am trying to calculate a rolling standard deviation for a variable
>> > > value_x:
>>
>> > > date obs_number value_x stdandard_deviation
>> > > 01012000 1 1 std(1)
>> > > 01022000 1 1.2 std(1,1.2)
>> > > 01032000 1 12 std(1,1.2,12)
>> > > 01042000 1 5 std(1,1.2,12,5)
>> > > 01052000 1 6 ....
>> > > 01012000 2 42
>> > > 01022000 2 1.422
>> > > 01032000 2 15232
>> > > 01042000 2 65
>> > > 01052000 2 63
>>
>> > > As you see, each next value of standard_deviation uses one extra
>> > > observation of value_x.
>>
>> > > Would appreciate your help!
>>
>> > Paul,
>>
>> > I see you have at least one solution, let me add another (which I
>> > learned some time ago from the BMDP software documentation). Depending
>> > on the number of observations and the nature of the data this method
>> > could be more accurate than using the common computational formula,
>> > sum(x**2)-sum(x)**2/N, for the sums of squares. You can drop whatever
>> > variables you don't want to keep. It would need to be modified slightly
>> > if you have any missing data, or if you wanted to use weighted data.
>>
>> > data want;
>> > set your_data;
>> > retain n mean v 0;
>> > n + 1;
>> > prior_mean = mean;
>> > mean + (value_x - mean)/n;
>> > v + (value_x - prior_mean) * (value_x - mean);
>> > standard_deviation = (v/(n-1))**.5 ;
>> > run;
>>
>> > Hope this is helpful,
>>
>> > Dan
>>
>> > Daniel J. Nordlund
>> > Research and Data Analysis
>> > Washington State Department of Social and Health Services Olympia, WA
>> > 98504-5204
>>
>> > -----Message Disclaimer-----
>>
>> > This e-mail message is intended only for the use of the individual or
>> > entity to which it is addressed, and may contain information that is
>> > privileged, confidential and exempt from disclosure under applicable law.
>> > If you are not the intended recipient, any dissemination, distribution or
>> > copying of this communication is strictly prohibited. If you have
>> > received this communication in error, please notify us immediately by
>> > reply email to Conn...@principal.com and delete or destroy all copies of
>> > the original message and attachments thereto. Email sent to or from the
>> > Principal Financial Group or any of its member companies may be retained
>> > as required by law or regulation.
>>
>> > Nothing in this message is intended to constitute an Electronic signature
>> > for purposes of the Uniform Electronic Transactions Act (UETA) or the
>> > Electronic Signatures in Global and National Commerce Act ("E-Sign")
>> > unless a specific statement to the contrary is included in this message.
>>
>> > While this communication may be used to promote or market a transaction
>> > or an idea that is discussed in the publication, it is intended to provide
>> > general information about the subject matter covered and is provided with
>> > the understanding that The Principal is not rendering legal, accounting,
>> > or tax advice. It is not a marketed opinion and may not be used to avoid
>> > penalties under the Internal Revenue Code. You should consult with
>> > appropriate counsel or other advisors on all matters pertaining to legal,
>> > tax, or accounting obligations and requirements.
| 
