Incompatible variables WHERE clause

[JediApprentice]

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.

[LinusH · Accepted solution]

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

[ballardw]

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;

[JediApprentice]

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

[Astounding]

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.

[JediApprentice]

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

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

[ballardw]

Try select quote(id) instead of the CATT.

[Shmuel]

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;

[JediApprentice]

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

[RW9]

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;