Date: Thu, 26 Apr 2007 07:00:15 -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: Rolling calculation
In-Reply-To: A<200704260213.l3Q1CRvW015090@mailgw.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"
Howard:
Great improvement! I only thought I needed all previous twelve
months' variable values. Actually what needed in each iteration is the
values of current month and twelve months ago.
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Howard Schreier <hs AT dc-sug DOT org>
Sent: Wednesday, April 25, 2007 9:13 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Rolling calculation
On Wed, 25 Apr 2007 12:52:58 -0500, Huang, JS <Huang.JS@PRINCIPAL.COM>
wrote:
>Here is an implementaion:
>
>data Test;
> input Month:monyy5. var1 var2;
> format Month:monyy5.;
> datalines;
>Jan04 3 20
>Feb04 5 25
>Mar04 6 30
>Apr04 7 35
>May04 8 40
>Jun04 9 45
>Jul04 10 50
>Aug04 11 55
>Sep04 12 60
>Oct04 13 65
>Nov04 14 70
>Dec04 15 75
>Jan05 13 20
>Feb05 15 25
>Mar05 16 30
>Apr05 17 35
>May05 18 40
>Jun05 19 45
>Jul05 20 50
>Aug05 21 55
>Sep05 22 60
>Oct05 23 65
>Nov05 24 70
>Dec05 25 75
>;
>
>proc sort data=Test;
> by Month;
>run;
>
>data Result(drop=i PastVarA1-PastVarA12 PastVarB1-PastVarB12);
> retain PastVarA1-PastVarA12 PastVarB1-PastVarB12 .;
> set Test;
> array PastVarA(12) PastVarA1-PastVarA12;
> array PastVarB(12) PastVarB1-PastVarB12;
> do i=dim(PastVarA) to 2 by -1;
> PastVarA(i)=PastVarA(i-1);
> PastVarB(i)=PastVarB(i-1);
> end;
> PastVarA(1)=var1;
> PastVarB(1)=var2;
> if _n_ ge 12 then do;
> var3=sum(of PastVarA(*))/sum(of PastVarB(*))*12;
> end;
>run;
>
>proc print data=Result;
>run;
>
>***** Output *****
> The SAS System 10:03
>Wednesday, April 25, 2007 23
>
> Obs Month var1 var2 var3
>
> 1 JAN04 3 20 .
> 2 FEB04 5 25 .
> 3 MAR04 6 30 .
> 4 APR04 7 35 .
> 5 MAY04 8 40 .
> 6 JUN04 9 45 .
> 7 JUL04 10 50 .
> 8 AUG04 11 55 .
> 9 SEP04 12 60 .
> 10 OCT04 13 65 .
> 11 NOV04 14 70 .
> 12 DEC04 15 75 2.37895
> 13 JAN05 13 20 2.58947
> 14 FEB05 15 25 2.80000
> 15 MAR05 16 30 3.01053
> 16 APR05 17 35 3.22105
> 17 MAY05 18 40 3.43158
> 18 JUN05 19 45 3.64211
> 19 JUL05 20 50 3.85263
> 20 AUG05 21 55 4.06316
> 21 SEP05 22 60 4.27368
> 22 OCT05 23 65 4.48421
> 23 NOV05 24 70 4.69474
> 24 DEC05 25 75 4.90526
>
Or
data result(drop = numer denom);
set test;
numer + var1 - sum(lag12(var1),0);
denom + var2 - sum(lag12(var2),0);
if _n_ > 12 then var3 = numer / denom;
run;
>-----Original Message-----
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>proc sas
>Sent: Wednesday, April 25, 2007 4:45 AM
>To: SAS-L@LISTSERV.UGA.EDU
>Subject: Rolling calculation
>
>Hi SAS-gurus,
>
>I have a dataset like this:
>
>Month var1 var2
>Jan04 3 20
>Feb04 5 25
>. . .
>. . .
>. . .
>. . .
>. . .
>Dec04 10 15
>Jan05 12 20
>. . .
>. . .
>. . .
>Mar07 . .
>
>The above "Dots" are not missing values. They are having some value.
>
>Now i need to make a calculation which will give me var3, and the
>calculation will be:
>
>var3 for Dec04: (sum of var1 for Jan04-Dec04 / sum of var2 for Jan04-
>Dec04)*12
>var3 for Jan05: (sum of var1 for Feb04-Jan05 / sum of var2 for Feb04-
>Jan05)*12
>.
>.
>.
>
>var3 for Mar07: (sum of var1 for Apr06-Mar07 / sum of var2 for Apr06-
>Mar07)*12
>
>I need to do this calculation:
>a) Whether using any do loop?
>b) Is there any other simple way taking this 12 month window and as
>each month progresses, the window advances by one month from start and
>at end.
>
>Thanks a lot in advance.
>
>
>-----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.
-----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.
