Date: Thu, 19 Apr 2007 11:14:39 -0500
Reply-To: "Huang, JS" <Huang.JS@PRINCIPAL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Huang, JS" <Huang.JS@PRINCIPAL.COM>
Subject: Re: calculating rolling standard deviation
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 <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 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.
