BookmarkSubscribeRSS Feed
steffick
Calcite | Level 5

Hi,

I've been running and staring at this code but can't figure out this error.

I believe my components are the same type - a datetime and date literals . I don't have an IN function in my code so where is that coming from? I included code that runs without problems at the bottom of the code, and it uses this admitdatetime between '2018-10-01' and '2019-9-30'. 

Diane

	proc sql;
	create table scr as select a.*, case when 
		b.patientsid is null then 'O' else 'I'
		end as IP_OR_OP
		from scr0 a left join inpat_inpatient b on 
	a.patientsid=b.patientsid
	where 
admitdatetime>='2018-10-01 00.00.00' and admitdatetime
<'2019-10-01 00.00.00' ; 

/*> Also tried between '20181001' and '20191001'
between '2018-10-01'
and '2019-10.01'
admitdatetime>='2018-10-01 00:00:000000' and admitdatetime
<'2018-10-01 00:00:000000'
 
AdmitDateTime is datetime20 format. Is the date constant not compatible
with datetime?

*/

 quit;


ERROR: Expression using greater than or equal (>=) has components that are of different data types.
NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL WHERE clause optimization.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
39       !   quit;

THIS WORKS FINE:
proc sql;
CONNECT TO SQLSVR AS CDW(DATAsrc=<project name> &SQL_OPTIMAL.);
create table inpat_inpatient as select * from
 connection to cdw
(select patientsid,admitdatetime,dischargedatetime from
src.inpat_inpatient
where admitdatetime between '2018-10-01'and '2019-09-30';);
disconnect from cdw;
quit;

 

1 REPLY 1
PaigeMiller
Diamond | Level 26
you can't compare admitdatetime, which is numeric, to '2018-10-01 00.00.00' which is a character string. SAS cannot compare a numeric to a character string. However if you use '01OCT2018:00:00:00'dt, SAS knows this is a numeric date/time value and then the comparison works.
--
Paige Miller

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 1 reply
  • 208 views
  • 0 likes
  • 2 in conversation