The error Expression using IN has components that are of different data types"

[steffick]

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;

[PaigeMiller]

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.