Paul ,

Try:

proc sql; create table example as select * from an_oracle_table where id in ( %single_quote_list (a b) ) ; quit;

Toby Dunn

From: Paul <paulvonhippel@YAHOO.COM> Reply-To: Paul <paulvonhippel@YAHOO.COM> To: SAS-L@LISTSERV.UGA.EDU Subject: macro inside PROC SQL Date: Mon, 10 Jul 2006 14:13:47 -0700

Here's a stumper. I can generate output using the IN operator in PROC SQL (example A).

proc sql; create table example as select * from an_oracle_table where id in ('a', 'b') ; quit;

But when I replace the list ('a', 'b') with a macro call, I get an error and no output -- even though the macro returns that same list:

proc sql; create table example as select * from an_oracle_table where id in %single_quote_list (a b) ; quit;

I'm thinking there must be some incompatibility between macros and PROC SQL? (I've used macros in PROC SQL before, but I may have done so only when the SQL was pass-through.)

Many thanks for any insights.

Best, Paul

P.S. Here I'm using the handy %single_quote_list macro, which I found on this list a while back. This macro takes a space-delimited list, encloses the elements in single quotes, and separates the elements with commas:

%macro single_quote_list( list ); %nrbquote(')%sysfunc( tranwrd( %Qsysfunc( compbl( &LIST ) ), %str( ), %str(', ')))%nrbquote(') %mend single_quote_list ;