Date: Fri, 20 Apr 2007 02:38:23 -0700
Reply-To: PBilin <pbilin@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: PBilin <pbilin@GMAIL.COM>
Organization: http://groups.google.com
Subject: Re: calculating rolling standard deviation
In-Reply-To: <B89C5B3C26FDEF4CA2BCBFAFC411AF8202ECB14F@PFGDSMMBX015.principalusa.corp.principal.com>
Content-Type: text/plain; charset="iso-8859-1"
Yes, it works with my data. Thank you!
Paul
On Apr 19, 5:14 pm, Huang...@PRINCIPAL.COM ("Huang, JS") wrote:
> 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:S...@LISTSERV.UGA.EDU] On Behalf Of
>
> PBilin
> Sent: Thursday, April 19, 2007 9:53 AM
> To: S...@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 <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.
>
> -----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
>
> ...
>
> read more »
| 
