topic Re: ERROR: Expression using IN has components that are of different data types. in SAS Programming

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

bhca60 — Mon, 19 Feb 2024 20:45:48 GMT

proc sql;
     /*connect to oracle as test (user=%SUPERQ(dra_u) password="&dra_p." path=huvhg); */

        create table now1(drop=crdttm) as
           select *, 
		  crdttm as act_dttm format=datetime24.3
			from connection to test
                (select distinct a.act_dttm,
                                 a.id,
                                 a.acttpcd,
                                 a.actrscd,
                                 a.actotcom_cd,                            
                                 a.inptid as emp_id,
                                 a.cmtrid,
                                 a.act_dttm as crdttm,
         			 c.mktcd,
                                 c.srcstcd,
                                 d.pur_id,
                                 d.bol_cd,
                                 d.clmplcd,
			         d.ent,
                             case when d.plancd in ('MMM','RRR','DDDD') then 'RNM'
                                  else 'INE'
                             end as Business,
                                 d.plancd
          
                    from dra.rsh_dra_snart_act as a

                       left join dra.rsh_dra_case as c
                          on a.id = c.id

                       left join dra.rsh_dra_nonmdm_mbr as d
                          on c.id = d.id 
				
                     	where a.act_dttm between (&start. and &end.)
                            and a.inptid in (&role1.)
                            and (c.srcstcd not in ('5','6') or c.srcstcd is null)
                            and (d.pur_id not in ('0001234','0005678') or d.pur_id is null)
    
                    );
                         
quit;

ERROR: Expression using IN 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.

Not sure what is causing this error. The macros run fine but it's something with the last few lines of the program.

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

PaigeMiller — Mon, 19 Feb 2024 21:04:54 GMT

From now on, please show us the log, instead of ERROR messages detached from the code. In this case, we need the PROC SQL log.

One of these three IN statements is a problem.

                     	where a.act_dttm between (&start. and &end.)
                            and a.inptid in (&role1.)
                            and (c.srcstcd not in ('5','6') or c.srcstcd is null)
                            and (d.pur_id not in ('0001234','0005678') or d.pur_id is null)

Please tell use the value of &role1. Also please tell us if INPTID is numeric or character, and if SRCSTCD is numeric or character, and if PUT_ID is numeric or character.

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

Astounding — Tue, 20 Feb 2024 03:21:27 GMT

We can't see your data. We can't see what is in your macro variables. So we are guessing. But here is my guess. SImplify this part of your query:


                            and (c.srcstcd not in ('5','6') or c.srcstcd is null)
                            and (d.pur_id not in ('0001234','0005678') or d.pur_id is null)

Even if the query were working successfully, you would get the same result by using:


                            and (c.srcstcd not in ('5','6') )
                            and (d.pur_id not in ('0001234','0005678'))

After all, if c.srcstcd is null, the first condition about it not being '5' or '6' would capture the observation. So even if this doesn't turn out to be the source of your error, at least you can simplify the program without changing the meaning of the query.

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

Patrick — Tue, 20 Feb 2024 11:33:33 GMT

Like the Error tells you there must be a mismatch between the type of your variable and the values for the IN clause.

                            and a.inptid in (&role1.)
                            and (c.srcstcd not in ('5','6') or c.srcstcd is null)
                            and (d.pur_id not in ('0001234','0005678') or d.pur_id is null)

For example if d.pur_id is of type numeric then the values in the IN clause shouldn't be in quotes as this indicates they are strings and not numbers.

What confuses me a bit:

The code itself is explicit pass-through code so if there is such a type mismatch then I would have expected to see some Oracle error and not - how it looks like - a SAS error message. Are you showing us the exact code that returns this error?