topic Re: Incompatible variables WHERE clause in SAS Programming

Incompatible variables WHERE clause

JediApprentice — Tue, 31 Jan 2017 16:20:22 GMT

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.

Re: Incompatible variables WHERE clause

LinusH — Tue, 31 Jan 2017 16:26:44 GMT

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

Re: Incompatible variables WHERE clause

ballardw — Tue, 31 Jan 2017 16:36:34 GMT

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;

Re: Incompatible variables WHERE clause

Astounding — Tue, 31 Jan 2017 16:53:45 GMT

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.

Re: Incompatible variables WHERE clause

JediApprentice — Tue, 31 Jan 2017 16:57:28 GMT

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

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

Re: Incompatible variables WHERE clause

JediApprentice — Tue, 31 Jan 2017 16:58:53 GMT

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

Re: Incompatible variables WHERE clause

ballardw — Tue, 31 Jan 2017 17:00:16 GMT

Try select quote(id) instead of the CATT.

Re: Incompatible variables WHERE clause

Shmuel — Tue, 31 Jan 2017 17:02:15 GMT

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;

Re: Incompatible variables WHERE clause

JediApprentice — Tue, 31 Jan 2017 17:07:48 GMT

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

Re: Incompatible variables WHERE clause

RW9 — Tue, 31 Jan 2017 17:09:05 GMT

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;