Date:         Mon, 10 Jul 2006 14:13:47 -0700
Reply-To:     Paul <paulvonhippel@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Paul <paulvonhippel@YAHOO.COM>
Organization: http://groups.google.com
Subject:      macro inside PROC SQL
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset="iso-8859-1"

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 ;