ERROR: Expression using IN has components that are of different data types.

[Jayreed34]

Running this code and coming up with the error above. Any suggestions on how to successfully run?

proc sql ; create table max_line_apc as
select legacy_claim_id
,line_id
,apc_fee
,max_apc_fee
from OPSURG6
where apc_fee not in (0, .)
order by legacy_claim_id, apc_fee desc
;
quit ;

[PaigeMiller · Accepted solution]

This indicates that when you are doing some sort of comparison, one of the variables is numeric and one of the variables is character/text, and so these cannot be compared. In your code, this is the comparison where this happens:

where apc_fee not in (0, .)

To fix this, you might want to change apc_fee to numeric (if possible), or try this

where input(apc_fee,32.) not in (0,.)

The Input function will turn a character variable into a numeric variable

[Jayreed34]

Thank you

[Jayreed34]

See screenshot:

[PaigeMiller]

When things are not working, we need to see the LOG (we need to see the entire log for this PROC SQL)