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

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

fredbell — Tue, 04 Jan 2011 15:52:01 GMT

Anyone know why i am getting this error, level_7_id are both of same data type?

44 PROC SQL;
45 CREATE table Calls_Handled_Temp AS
46 SELECT A.SiteID,A.Indicator_OutSource, B.ReportTime,B.Level_7_ID, B.agentcallshandled
47 FROM level7 A FULL JOIN jennifer.tbl_vccartsceintrvl B
48 ON (A.Level_7_ID = B.Level_7_ID)
49 WHERE
50 ReportTime >= &MTDfromdate and ReportTime <= &MTDTodate
51 order by ReportTime asc;
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.
52
53 QUIT;

Fred

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

Paige — Tue, 04 Jan 2011 16:20:49 GMT

Perhaps you made a typo? There is no "IN" expression shown in your code.

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

deleted_user — Tue, 04 Jan 2011 17:51:51 GMT

Please check where condition and Macro Variable value and data type.
If you want to check, please run without where condition then see.
If you are not getting any error then it would be problem with the Macro variable .

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

Peter_C — Wed, 05 Jan 2011 18:16:09 GMT

> Anyone know why i am getting this error, level_7_id
> are both of same data type?
>
>
>
> 44 PROC SQL;
> 45 CREATE table Calls_Handled_Temp AS
> 46 SELECT A.SiteID,A.Indicator_OutSource,
> B.ReportTime,B.Level_7_ID, B.agentcallshandled
> 47 FROM level7 A FULL JOIN
> jennifer.tbl_vccartsceintrvl B
> 48 ON (A.Level_7_ID = B.Level_7_ID)
> 49 WHERE
> 50 ReportTime >= &MTDfromdate and ReportTime <=
> &MTDTodate
> 51 order by ReportTime asc;
> 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.
> QUIT;
> red

unfortunately a GE and LE symbol have mis-directed the browser surface

The original message text may now be visible as quoted above, along with the hint

NOTE: The IN referred to may have been transformed from an OR to an IN at some point during PROC SQL where clause optimization.

I would suggest using option SYMBOLGEN and converting the testing of ReportTime into a "between" test like

where reportTime between &MTDfromdate and &MTDtodate

good luck
peterC