BookmarkSubscribeRSS Feed
hellind
Quartz | Level 8

 

 

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?

 

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8
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
Super User

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
Quartz | Level 8
data DT.TEST;
  infile datalines dsd truncover;
  input Entity:$255. Disclosure_Date:DATETIME20. _TEMA001:DATE9.;
datalines4;
ROW Asset,01SEP2016:00:00:00,01SEP2016
;;;;
Tom
Super User Tom
Super User

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
Quartz | Level 8
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
Quartz | Level 8

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
Super User Tom
Super User

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
Fluorite | Level 6

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 10084 views
  • 3 likes
  • 5 in conversation