DATA Step, Macro, Functions and more

Incompatible variables WHERE clause

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 123
Accepted Solution

Incompatible variables WHERE clause

 

 

First I am putting a list of IDs in a macro variable from a dataset, then I'm using that macro variable in a where dataset option for an different incoming dataset:

 

proc sql noprint;                              
 select ID into :ID_List_2010 separated by ' '
 from New_ID
 where date < '01jan2011'd;
quit;

data want;
  set have(where=(ID in(&ID_List_2010)));
run;

But I get this error:

 WHERE clause operator requires compatible variables.

 

-ID in have is char of 15 length.

-ID in New_ID is a char of 7 length.

-Some IDs start with a 0.

 

How do I get around this error? Thanks.


Accepted Solutions
Solution
‎01-31-2017 11:56 AM
Super User
Posts: 5,255

Re: Incompatible variables WHERE clause

%put &ID_List_2010;
and you'll see what's happening.
Or use OPTIONS SYMBOLGEN;
Data never sleeps

View solution in original post


All Replies
Solution
‎01-31-2017 11:56 AM
Super User
Posts: 5,255

Re: Incompatible variables WHERE clause

%put &ID_List_2010;
and you'll see what's happening.
Or use OPTIONS SYMBOLGEN;
Data never sleeps
Super User
Posts: 10,483

Re: Incompatible variables WHERE clause

And post actual log. You have TWO where clauses? Which one threw the error? Could your DATE variable not be a SAS date value and is perhaps character?

 

And no need for macro variables if the only use to match them in HAVE.

proc sql;
   create table want as
   select have.*
   from (select * from New_id where date<'01JAN2011'd) left join
        have on New_id.id = have.id;
quit;
Frequent Contributor
Posts: 123

Re: Incompatible variables WHERE clause

@ballardw Sorry I was not more specific about which one was having the issue - the where clause with the date was fine, it was the one with the ID.

Super User
Posts: 5,079

Re: Incompatible variables WHERE clause

To illustrate what Linus is getting at ...

 

Your macro variable looks something like this:  abc def xyz

 

So your SET statement looks something like this:

 

set have (where=(ID in (abc def xyz)));

 

That's the wrong syntax when ID is a character variable.  You would need:

 

set have (where=(ID in ("abc" "def" "xyz")));

 

So when SQL creates your macro variable, you need to add quotes around the ID values that it extracts.

 

Frequent Contributor
Posts: 123

Re: Incompatible variables WHERE clause

I see, yes. What I did to solve it was:

 

select catt("'",ID,"'") into :ID_List_2010 separated by ', '

 

Super User
Posts: 10,483

Re: Incompatible variables WHERE clause

Try select quote(id) instead of the CATT.

Trusted Advisor
Posts: 1,369

Re: Incompatible variables WHERE clause

You can use either nested sql select as @ballardw posted, 

or change your code to:

proc sql noprint;                              
 select ID into :ID_List_2010 separated by '" "'
 from New_ID
 where date < '01jan2011'd;
quit;

data want;
  set have(where=(ID in("&ID_List_2010")));
run;
Frequent Contributor
Posts: 123

Re: Incompatible variables WHERE clause

[ Edited ]

@Shmuel I see, that one's pretty interesting because you account for the missing quotes at the beginning and end of the list by putting quotes around the macro variable reference.

Super User
Super User
Posts: 7,392

Re: Incompatible variables WHERE clause

Why not save yourself a bit of coding, and messing around with macro lists:

proc sql;
  create table WANT as
  select *
  from   HAVE
  where ID in (select ID from NEW_ID where DATE < '01JAN2011'd);
quit;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 166 views
  • 4 likes
  • 6 in conversation