Date: Thu, 16 Sep 2010 15:53:33 -0700
Reply-To: "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject: Re: Arithmetic with dates in PROC SQL
In-Reply-To: A<16FD64291482A34F995D2AF14A5C932C097381C9@MAIL002.prod.ds.russell.com>
Content-Type: text/plain; charset="us-ascii"
Here is a more specific reference page:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.qmf
9.doc.using/dsqk2sql1012806.htm#dsqk2sql1012806
Mark
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Terjeson, Mark
Sent: Thursday, September 16, 2010 3:47 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Arithmetic with dates in PROC SQL
Hi,
As you know, anything in between the paretheses
for the FROM CONNECTION TO has to be the syntax
that goes with the environment being run in.
i.e. the "pass-through" code which is all the
code in between the parens must be DB2 syntax,
and everything outside the parens is SAS syntax.
So you would be heading to your IBM DB2 manuals
for date functions or date literal operators.
e.g.
how about:
WHERE DATE BETWEEN (&FROM - 7 DAYS)
AND (&TO + 7 DAYS)
See QMF section of DB2 date handling:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/
com.ibm.db29.doc.sqlref/db2z_scalarfunctionsintro.htm
Hope this is helpful.
Mark Terjeson
Investment Business Intelligence
Investment Management & Research
Russell Investments
253-439-2367
Russell
Global Leaders in Multi-Manager Investing
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Chaitovsky, Avi-Gil
Sent: Thursday, September 16, 2010 2:35 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Arithmetic with dates in PROC SQL
I'm guessing this question has probably come up before, but I haven't
been able to find an answer. I'm using the following macro variables in
a proc sql step to pull all records in a certain date range:
%LET FROM = '2010-05-01';
%LET TO = '2010-05-31';
PROC SQL;
CONNECT TO DB2 (DSN=... UID=... PWD=...);
CREATE TABLE MYDATA AS SELECT * FROM CONNECTION TO DB2
(
SELECT *
FROM Table
WHERE DATE BETWEEN &FROM AND &TO
;
QUIT;
What I'd like to do is be able to expand my date range by 7 days in
either direction by using something like
WHERE DATE BETWEEN (&FROM-7) AND (&TO+7)
This doesn't seem to work - any ideas on how to do this?
Avi-Gil Chaitovsky
WellPoint, Inc.
Empire BlueCross BlueShield
HealthCare Analytics
Business Information Analyst II
165 Broadway, 14th Floor
New York, NY 10006
Avi-Gil.Chaitovsky@anthem.com <mailto:Avi-Gil.Chaitovsky@anthem.com>
(212) 476-1483
CONFIDENTIALITY NOTICE: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential
and privileged information or otherwise protected by law. Any
unauthorized
review, use, disclosure or distribution is prohibited. If you are not
the
intended recipient, please contact the sender by reply e-mail and
destroy
all copies of the original message.
