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 — Fri, 08 Oct 2010 15:46:32 GMT

Hi everyone!

I'm having an issue with the below code and the error in the Subject.

%let MTDFromDate= '01-oct-2010:00:00:000'dt;
%let MTDToDate = '10-oct-2010:00:00:000'dt;
proc sql;
create table test as
select * from jennifer.vccart_sceintrvl
WHERE ReportTime ge &MTDfromdate and ReportTime le &MTDTodate;
quit;

Log

212 %let MTDFromDate= '01-oct-2010:00:00:000'dt;
213 %let MTDToDate = '10-oct-2010:00:00:000'dt;
214 proc sql;
215 create table test as
216 select * from jennifer.vccart_sceintrvl
217 WHERE ReportTime ge &MTDfromdate and ReportTime le &MTDTodate;
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.
218 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.39 seconds
cpu time 0.01 seconds

Help Please

Fred

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

sbb — Fri, 08 Oct 2010 16:51:21 GMT

The diagnostic message is telling you that your variable/column ReportTime in your source/input file is not in the same format as the SAS DATETIME literal constant you have coded. You will need to address this, possibly, by using an INPUT function in your WHERE clause to convert the variable from CHARACTER to NUMERIC using the appropriate SAS INFORMAT.

A search of the SAS support website with your ERROR message will also turn up some suitable references, as well.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

proc sql input function where clause site:sas.com

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

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

Ksharp — Mon, 11 Oct 2010 08:12:18 GMT

Hi. I am curious that my code is worked.
Perhaps, the form of your datetime is not right.Should be '10nov2005:3:49:19am'dt or '10nov2005:03:49:19'dt

[pre]
%let MTDFromDate= '01jan1998'd;
%let MTDToDate = '01jan2005'd;
proc sql;
create table test as
select * from sashelp.buy
WHERE date ge &MTDfromdate and date le &MTDTodate;
quit;
[/pre]

Ksharp

Message was edited by: Ksharp

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

fredbell — Tue, 12 Oct 2010 11:33:00 GMT

Hi Ksharp

Sorry your code resulted in the below error.

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

Fred

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

sbb — Tue, 12 Oct 2010 12:30:01 GMT

Top the OP: did you investigate / research my reply, especially by searching the SAS support website using the error message as your search argument? You will find that others have encountered the same condition.

Scott Barry
SBBWorks, Inc.

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

fredbell — Tue, 12 Oct 2010 14:08:47 GMT

Hi sbb

Yes i have been looking all weekend but can't find anything that explains it so i can understand.

Reading and more reading, tried many many variations without success.

If anyone has an example please that would be great.

Fred

Message was edited by: fredbell

Message was edited by: fredbell Message was edited by: fredbell

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

Reeza — Tue, 12 Oct 2010 16:39:58 GMT

Look up what data type the variable "ReportTime" has on the table Jennifer. vccart_sceintrvl.

Then change your macro variables to have the same format.

Can't provide an example since we don't know what format that is in. This is the simplest solution IMO. Scott's solution and the examples found by searching would work as well...

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

fredbell — Tue, 12 Oct 2010 17:21:19 GMT

So very frustrating, below code finally worked.

%let MTDFromDate= '2010-10-01 00:00:00.000000';
%let MTDToDate = '2010-10-12 00:00:00.000000';
proc sql;
create table test as
select * from jennifer.vccart_sceintrvl
WHERE ReportTime ge &MTDfromdate and ReportTime le &MTDTodate;
quit;

Thanks

Fred

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

Robert_Bardos — Wed, 13 Oct 2010 07:27:47 GMT

This seems to indicate that ReportTime is a character variable in your dataset, while your first attempts compared that to a SAS datetime and so to a numeric value.

(I say "seems" as I can't test/verify for the time being)