topic Re: Where Date >= '01Jul2015'd not working in SAS Programming

Where Date >= '01Jul2015'd not working

hellind — Wed, 28 Jun 2017 11:12:01 GMT

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?

Re: Where Date >= '01Jul2015'd not working

RW9 — Wed, 28 Jun 2017 11:33:49 GMT

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;

Re: Where Date >= '01Jul2015'd not working

Kurt_Bremser — Wed, 28 Jun 2017 11:36:50 GMT

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-data-AKA-generate/ta-p/258712 to convert dataset test to a datastep and post that here.

Edit: added entity and disclosure_date in the select.

Re: Where Date >= '01Jul2015'd not working

hellind — Wed, 28 Jun 2017 23:49:33 GMT

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.

Re: Where Date >= '01Jul2015'd not working

hellind — Thu, 29 Jun 2017 00:00:24 GMT

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

Re: Where Date >= '01Jul2015'd not working

Tom — Thu, 29 Jun 2017 00:22:32 GMT

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.

Re: Where Date >= '01Jul2015'd not working

hellind — Thu, 29 Jun 2017 00:28:32 GMT

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.

Re: Where Date >= '01Jul2015'd not working

hellind — Thu, 29 Jun 2017 00:24:58 GMT

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.

Re: Where Date >= '01Jul2015'd not working

Tom — Thu, 29 Jun 2017 02:58:36 GMT

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;

Re: Where Date >= '01Jul2015'd not working

wilson — Wed, 07 Apr 2021 21:04:49 GMT

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);