Shiling brings up a good point. I have done this in the past. I would start by sticking the desired column values in double quotes into a macro variable. Then I would TRANSLATE the double quotes to single quotes. To illustrate:

proc sql noprint ; select quote(trim(left(name))) into : namelist separated by "," from sashelp.class ; quit ;

%put BEFORE <<<&NAMELIST >>> ;

%let namelist = %sysfunc(translate(%superq(namelist),%nrbquote(%'),%nrbquote(%"))) ;

%put AFTER <<<&NAMELIST>>> ;

Venky Chakravarthy

On Fri, 24 Mar 2006 10:40:37 -0800, shiling99@YAHOO.COM wrote:

>Richard, > >I don't know there is an elegant way to do it. But If a macro variable >is defined already in a list format, I will kick in a data step to >re-assemble another macro variable with single quotes. The same logic >can also be used in macro framework. > > >636 %let wherecdtn=a, b, c, d, 1, e; >637 >638 data _null_; >639 length vlist $32000; >640 retain vlist; >641 do i=1 to 5000 while (scan("&wherecdtn",i,',') ne ' '); >642 x="'"||trim(left(scan("&wherecdtn",i,',')))||"'"; >643 vlist=catx(',',vlist ,x); >644 end; >645 call symput ('wherecdtn2',trim(vlist)); >646 run; > >NOTE: DATA statement used (Total process time): > real time 0.00 seconds > cpu time 0.00 seconds > > >647 >648 %put >>>&wherecdtn2<<<; >>>>'a','b','c','d','1','e'<<< >649 >650 %let wherecdtn="a", "b", "c" , "d", "1", "e"; >651 >652 data _null_; >653 length vlist $32000; >654 length x $100; >655 retain vlist; >656 do x=&wherecdtn; >657 x="'"||trim(x)||"'"; >658 vlist=catx(',',vlist ,x); >659 end; >660 call symput ('wherecdtn2',trim(vlist)); >661 run; > >NOTE: DATA statement used (Total process time): > real time 0.00 seconds > cpu time 0.00 seconds > > >662 >663 %put >>>&wherecdtn2<<<; >>>>'a','b','c','d','1','e'<<< > > > >Richard A. DeVenezia wrote: >> Is there a 'better' way to use a macro var as a string literal in an >> Oracle side query ? >> >> Current way: >> >> %let name = Zaphod; >> %let namesq = %str(%'&name.%'); >> proc sql; >> connect to oracle (...); >> create view whatever as >> select * from connection to oracle >> ( >> select * from ImportantStuff >> where name = &namesq. >> );