Where Date >= '01Jul2015'd not working

[hellind]

When I run this code 

proc sql;
	select datepart(round(disclosure_date,.1)) format date9.
	from core.bum_disclosure
	where  entity like 'Row%'
/*		and datepart(round(disclosure_date,.1)) >= '01Jul2015'd*/
 ;quit;

It retuns 

SAS Output

01SEP2016

Then I run same query

proc sql;
	select datepart(round(disclosure_date,.1)) format date9.
	from core.bum_disclosure
	where  entity like 'Row%'
		and datepart(round(disclosure_date,.1)) >= '01Jul2015'd
 ;quit;

It returns zero records. Why?

[RW9]

Well, question 1 is going to be why are you rounding a date?

Second question is going to be "where is the test data in datastep form which we always ask for"

The below works fine:

data have;
  entity="Row1"; 
  disclosure_date="01SEP2016T00:10:00"dt;
run;
proc sql;
  create table want as 
	select datepart(disclosure_date) format date9.
	from have
	where  entity like 'Row%'
		and datepart(disclosure_date) >= '01Jul2015'd;
quit;

[hellind]

Even without rounding the problem occurs. I rounded up to zero decimal places because some years ago in this forum I encountered similar issue. For some reason there was a decimal in dates after importing.

[Kurt_Bremser]

Please run

proc sql;
  create table test as
	select entity, disclosure_date, datepart(round(disclosure_date,.1)) format date9.
	from core.bum_disclosure
	where  entity like 'Row%'
/*		and datepart(round(disclosure_date,.1)) >= '01Jul2015'd*/
 ;
quit;

and then use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... to convert dataset test to a datastep and post that here.

Edit: added entity and disclosure_date in the select.

[hellind]

data DT.TEST;
  infile datalines dsd truncover;
  input Entity:$255. Disclosure_Date:DATETIME20. _TEMA001:DATE9.;
datalines4;
ROW Asset,01SEP2016:00:00:00,01SEP2016
;;;;

[Tom]

So that example data doesn't match the first part of the WHERE clause.

ROW is not like 'Row%' since it has uppercase letters instead of lowercase letters.

[hellind]

CORE is an ODBC connection to Access 2013. So 'Row%' do returns a record. And after I put an additional where condition for Disclosure_Date it does not return an record.

[hellind]

When I query core.bum_disclosure, CORE is an ODBC connect to Access 2013.

When I export the data to SAS

data newclients;
set core.bum_disclosure;
run;

The same query in my first post run fine on work.newclients. 

 So I will move on and attribute to something with my old SAS connector, I guess. 

Thanks all for the quick help.

[Tom]

It looks like your external database is doing case insenstive comparisons on the variable ENTITY. But when you used the SAS specific functions DATEPART() and ROUND() then SAS could no longer push the WHERE clause into the database and so the LIKE clause ran on the SAS side instead where the comparison is case sensitive.

Why not just avoid using those functions?  If you want to compare to a specific datetime value use a datetime literal instead of a date literal.

proc sql;
select disclosure_date format=dtdate9.
from core.bum_disclosure
where entity like 'Row%'
  and disclosure_date >= '01Jul2015:00:00'dt
;
quit;

[wilson]

Your date variable in data is in DATE9. informat. Use dhms function to make both sides of dates into same format. Then your where statement need to be changed as like this and then it should work.

Where dhms(Date,0,0,0) >= dhms('01JUL2015'd,0,0,0);