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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 457 views
  • 0 likes
  • 2 in conversation