LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2007, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.


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