Date:         Sat, 19 Sep 2009 15:06:43 -0400
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: Presumably equivalent date filters, different results
Comments: To: Dimitri Shvorob <dimitri.shvorob@VANDERBILT.EDU>

Dimitri,

I get two different results from your initial proc sql statements.

If handoffdate is actually a datetime variable, as indicated in your second
set of statements, then the first set returns 0 records.

Art
---------
 On Sat, 19 Sep 2009 14:35:18 -0400, Dimitri Shvorob
<dimitri.shvorob@VANDERBILT.EDU> wrote:

>Do you think that PROC SQL WHERE filters
>
>b.handoffdate between '01jan2009'd and '31dec2009'd
>
>and
>
>year(datepart(b.handoffdate)) = 2009
>
>are equivalent?
>
>I do, and, reassuringly, the two queries below yield the same number of
>records.
>
>  proc sql;
>   create table set1 as
>   select distinct handoffdate
>   from dst
>   where handoffdate between '01jan2009'd and '31dec2009'd;
>
>   create table set2 as
>   select distinct handoffdate
>   from dst
>   where year(datepart(handoffdate)) = 2009;
>   run;
>
>However, the join below disagrees:
>
> create table temp1 as select
>input(substr(put(accountnumber,10.),1,7), 7.0) as base_number, datepart
>(handoffdate) as
>handoff_date, fcyamountsigned, ccycode as fcycode from outflows a join dst
>b on
>a.modcode = b.modcode and a.txncode = input(b.txncode,3.) and a.subtxncode
=
>input(b.subtxncode,3.) where lcyamountsigned <0 and b.handoffdate
>[CONDITION] and accountnumber between 1e7 and (1e9 - 1) ;
>
>Depending on which condition I use, TEMP1 has 2K or 20K observations. (2K
>with the 'between' version).
>
>Can anyone offer an explanation?
>
>Thank you.