Date:         Sat, 19 Sep 2009 16:08:45 -0400
Reply-To:     Scott Barry <sbarry@SBBWORKS.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Scott Barry <sbarry@SBBWORKS.COM>
Subject:      Re: Presumably equivalent date filters, different results

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.

You really have too much other crap (filtering logic) in your query - and as
stated you are either working with DATE or DATETIME variables which is also
not clearly indicated.

Suggest some self diagnosis with reading about DATE and DATETIME variables
(and specifics with PROC SQL), especially if your intended source is an
external DBMS.

Other info about SAS version, OS platform, remote or local SAS access
particulars are also useful with posts.

Scott Barry
SBBWorks, Inc.

SAS Language Concepts: About SAS Date, Time, and Datetime Values
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a002200738.htm

SAS SQL Procedure: Programming with the SQL Procedure
http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001340018.htm