Good suggestions given above ... I would like to add an explanation as to why this macro didn't work as originally written.
First, you should always turn on options mprint; before you run any macro, this will show in the log the code that SAS generates, making the whole thing easier to debug.
You have a syntax error, you should be using
%str(%')
twice, as otherwise the single quote is not properly resolved when SAS executes; the % sign indicating to the %STR() function that there is a single unmatched quote.
Next you need the %unquote() function:
where name in (%unquote(%str(%')%bquote(&id_list)%str(%')))
but the reasoning isn't particularly simple to explain. All I know is that when it seems like your code is properly generated in the log, and yet SAS reports errors, %unquote() often fixes the problem.
The moral of the story is that creating macro strings with quotes and commas can be difficult, and the simpler methods (such as that provided by @novinosrin of creating a quoted string works much better and with many fewer complications, and so I highly recommend that you don't try to add your own quotes around the value of a macro variable unless nothing else seems to work.
... View more