BookmarkSubscribeRSS Feed
fredbell
Fluorite | Level 6
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
8 REPLIES 8
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Super User
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
Fluorite | Level 6
Hi Ksharp

Sorry your code resulted in the below error.

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

Fred
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Fluorite | Level 6
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
Super User
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
Fluorite | Level 6
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
Fluorite | Level 6
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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 28724 views
  • 0 likes
  • 5 in conversation