When I run the following code in SAS, I was told that the data type between ‘not in’ were different and can’t procede to run. But I’ve already used ‘input’ to convert the data type, so I don’t know what’s going wrong.
NOTE: DAT and EGDAT are not from the same table and have different original data type.
case when (input(DAT, yymmdd10.)) NOT IN (SELECT input(EGDAT, yymmdd10.) FROM SOURCE.EG001 WHERE VISIT IN ('SCREENING' , 'CYCLE1_DAY1' , ...))
Advertisement
Answer
Either the data columns are not as you presume, or there is some other part of the query that is causing the message.
Example:
The SQL code similar to yours and there are no errors.
data claims(label="Simulated dates"); length patid 8 DAT $8; call streaminit(20200423); do patid = 1 to 100; do _n_ = '01jan2020'd to '23apr2020'd; DAT = put (_n_, yymmddn8.); if rand ('uniform') < 0.05 then output; end; end; run; data visits(label="Simulated visit reasons"); length patid 8 EGDAT $22; set claims(rename=dat=egdat); call streaminit(20200423); _n_ = rand('uniform'); length visit $25; select; when (_n_ < 0.15) visit = 'SCREENING'; when (_n_ < 0.30) visit = 'CANCELLED'; when (_n_ < 0.45) visit = 'CYCLE1_DAY1'; when (_n_ < 0.55) visit = 'ACHES'; when (_n_ < 0.75) visit = 'PAINS'; when (_n_ < 0.95) visit = 'CYCLE1_DAY2'; otherwise visit = 'PLEASANT'; end; output; run; proc sql; create table want as select claims.patid , claims.DAT , case when input(DAT, yymmdd10.) not in ( select input(egdat, yymmdd10.) from visits where visits.patid = claims.patid and visit in ('SCREENING', 'CYCLE1_DAY1', 'CYCLE2_DAY2') ) then 'NOT A SCREENING CLAIM' else 'SEEMS LIKE A SCREENING CLAIM' end as visit_classification from claims order by patid, dat ; quit; ----- LOG ----- NOTE: Table WORK.WANT created, with 574 rows and 3 columns. 491 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.16 seconds cpu time 0.01 seconds