Date:         Thu, 16 Sep 2010 16:39:54 -0500
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: Arithmetic with dates in PROC SQL
Comments: To: "Chaitovsky, Avi-Gil" <Avi-Gil.Chaitovsky@anthem.com>
In-Reply-To:  <D318FFABEC06504E9EB926116EF6CC0605DB1894@VAPWVBE022.us.ad.wellpoint.com>
Content-Type: text/plain; charset=ISO-8859-1

Your problem is probably that you're using characters that SQL is converting
into a date, not *technically* a date.  You need to use your DB's language
to convert it into a date and then increment it by 7/decrement it by 7.  I
don't know DB2, but if it were Oracle it would be something approximating

where datevar between (to_date(&from,'YYYY-MM-DD')-7) and
(to_date('YYYY-MM-DD')+7)

-Joe

On Thu, Sep 16, 2010 at 4:34 PM, Chaitovsky, Avi-Gil <
Avi-Gil.Chaitovsky@anthem.com> wrote:

> 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.
>