LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (September 2010, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: "Chaitovsky, Avi-Gil" <Avi-Gil.Chaitovsky@ANTHEM.COM>
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.


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