```Date: Thu, 19 Apr 2007 12:22:57 -0400 Reply-To: "data _null_;" Sender: "SAS(r) Discussion" From: "data _null_;" Subject: Re: calculating rolling standard deviation Comments: To: PBilin In-Reply-To: <1176994361.512818.292920@n76g2000hsh.googlegroups.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed A visit to support.sas.com to view examples of the LAG function yields the following. I'm not sure if it is a rolling STD but it is the STD of the current and previous 2 observations. proc plan; factors subject=20 ordered size=10 ordered y=1 of 100 ; output out=work.rolling(drop=size) run; data rollingSTD; cnt = 0; do until(last.subject); set rolling; by subject; lagY1=lag1(y); lagY2=lag2(y); lagY3=lag3(y); array _lags[*] lagY:; cnt + 1; do i=cnt to dim(_lags); _lags[i]=.; end; if n(of _lags[*]) eq dim(_lags) then std = std(of _lagS[*]); output; end; *drop lagY: cnt i; run; proc print; run; On 4/19/07, PBilin wrote: > 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. > ```

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