```Date: Thu, 19 Apr 2007 11:14:39 -0500 Reply-To: "Huang, JS" Sender: "SAS(r) Discussion" From: "Huang, JS" Subject: Re: calculating rolling standard deviation Comments: To: PBilin In-Reply-To: A<1176994361.512818.292920@n76g2000hsh.googlegroups.com> Content-Type: text/plain; charset="us-ascii" See if the following fits your need. data my_data; input observation_no p; datalines; 1 1 1 2 1 3 1 4 2 3 2 5 2 10 2 13 2 18 ; data Temp; retain SumOfX SumOfX2 xCount; 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; run; data Result(keep=observation_no p PopulationStdDev Last3PopulationStdDev); set Temp; PopulationStdDev=((SumOfX2-SumOfX**2/xCount)/xCount)**0.5; Last3Observation_no=lag3(observation_no); Last3SumOfX2=lag3(SumOfX2); Last3SumOfX=lag3(SumOfX); if observation_no eq Last3Observation_no then do; Last3PopulationStdDev=(((SumOfX2-Last3SumOfX2)-(SumOfX-Last3SumOfX)**2/3 )/3)**0.5; end; run; proc print data=Result; run; ***** Output ***** The SAS System 08:18 Thursday, April 19, 2007 50 observation_ Population Last3Population Obs no p StdDev StdDev 1 1 1 0.00000 . 2 1 2 0.50000 . 3 1 3 0.81650 . 4 1 4 1.11803 0.81650 5 2 3 0.00000 . 6 2 5 1.00000 . 7 2 10 2.94392 . 8 2 13 3.96074 3.29983 9 2 18 5.41849 3.29983 -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of PBilin Sent: Thursday, April 19, 2007 9:53 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: calculating rolling standard deviation One more question. I am trying to adjust the proposed code to calculate rolling standard deviation for the last 3 observations only. 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. -----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 Connect@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