DATA Step, Macro, Functions and more

Where Date >= '01Jul2015'd not working

Reply
Frequent Contributor
Posts: 90

Where Date >= '01Jul2015'd not working

 

 

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?

 

Super User
Super User
Posts: 7,392

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

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;
Frequent Contributor
Posts: 90

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

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.
Super User
Posts: 6,928

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

[ Edited ]

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 90

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

[ Edited ]
data DT.TEST;
  infile datalines dsd truncover;
  input Entity:$255. Disclosure_Date:DATETIME20. _TEMA001:DATE9.;
datalines4;
ROW Asset,01SEP2016:00:00:00,01SEP2016
;;;;
Super User
Super User
Posts: 6,499

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

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.

Frequent Contributor
Posts: 90

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

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.
Frequent Contributor
Posts: 90

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

[ Edited ]

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.

Super User
Super User
Posts: 6,499

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

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;
Ask a Question
Discussion stats
  • 8 replies
  • 111 views
  • 2 likes
  • 4 in conversation