01-31-2017 11:20 AM
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.
01-31-2017 11:36 AM
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;
01-31-2017 11:58 AM
@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.
01-31-2017 11:53 AM
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.
01-31-2017 12:02 PM
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;
01-31-2017 12:06 PM - edited 01-31-2017 12:07 PM
@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.
01-31-2017 12:09 PM
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;
Need further help from the community? Please ask a new question.