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

[fredbell]

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

[sbb]

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

[Ksharp]

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

[fredbell]

Hi Ksharp

Sorry your code resulted in the below error.

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

Fred

[sbb]

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.

[fredbell]

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

[Reeza]

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...

[fredbell]

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

[Robert_Bardos]

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)