BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

 

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
%put &ID_List_2010;
and you'll see what's happening.
Or use OPTIONS SYMBOLGEN;
Data never sleeps

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20
%put &ID_List_2010;
and you'll see what's happening.
Or use OPTIONS SYMBOLGEN;
Data never sleeps
ballardw
Super User

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
Pyrite | Level 9

@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
PROC Star

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
Pyrite | Level 9

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

 

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

 

ballardw
Super User

Try select quote(id) instead of the CATT.

Shmuel
Garnet | Level 18

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
Pyrite | Level 9

@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
Diamond | Level 26 RW9
Diamond | Level 26

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 1644 views
  • 4 likes
  • 6 in conversation