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'.
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
admitdatetime>='2018-10-01 00.00.00' and admitdatetime
<'2019-10-01 00.00.00' ;
/*> Also tried between '20181001' and '20191001'
admitdatetime>='2018-10-01 00:00:000000' and admitdatetime
AdmitDateTime is datetime20 format. Is the date constant not compatible
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:
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
where admitdatetime between '2018-10-01'and '2019-09-30';);
disconnect from cdw;
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.