```Date: Thu, 19 Apr 2007 11:48:36 -0400 Reply-To: "Howard Schreier " Sender: "SAS(r) Discussion" From: "Howard Schreier " Subject: Re: calculating rolling standard deviation On Thu, 19 Apr 2007 07:52:41 -0700, PBilin 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 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. ```

Back to: Top of message | Previous page | Main SAS-L page