Date:         Fri, 1 Sep 2006 15:32:54 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Fw: Macro string from Proc Sql
Comments: To: KMyers1@CLEARWIRE.NET
Comments: cc: auto208611@hushmail.com
In-Reply-To:  <04f401c6cdd9$a37ca4d0$6502a8c0@speedy>
Content-Type: text/plain; format=flowed

Good job Kevin, beat me to the punch while I was outside enjoying a few
breif moments in the lovely weather watching the squirels and drinking
myfourth or fifth cup of morning Java.

Yes the Qoute( Name ) is a better solution in my eyes to and to expand upon
what I was saying earlier you dont even need the commas as the where clause
is a SAS thing not a SQL thing.  As such SAS is not by its nature a comma
separated language like SQL is.

Consider the following:

Data One ;
Do Text = 'A' , 'B' , 'C' , 'D' , 'E' ;
  Output ;
End ;
Run ;


Proc SQL ;
Select Text
  From One
    Where Text In ( 'A' "B" 'C' ) ; /* <---Me having fun with the Quotes and
Look O my no comma*/
Quit ;


Toby Dunn

When everything is coming at you all at once, your in the wrong lane.

A truly happy person is someone who can smile and enjoy the scenery on a
detour.





From: Kevin Myers <KMyers1@CLEARWIRE.NET>
Reply-To: Kevin Myers <KMyers1@CLEARWIRE.NET>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Fw: Macro string from Proc Sql
Date: Fri, 1 Sep 2006 10:16:43 -0500

Toby already provided this basic recommendation, but I'm going to add a bit
more explanation and an example...

A slightly more reliable solution than using "''" (assuming your application
doesn't care whether you use single quotes or double quotes) is to use the
quote function.  Using the quote function will protect you from potential
problems if your data values contain embedded quotes (whether single or
double), and your values are going to used directly in SAS code (as the
arguments to an IN clause, for example).

proc sql noprint;
  select quote(name) into :list separated by ',' from sashelp.class;
  quit;
%put &list;


----- Original Message -----
From: <auto208611@hushmail.com>
To: <sas-l@uga.edu>
Sent: Friday, September 01, 2006 08:28
Subject: Re: Macro string from Proc Sql


 > Thank you both, problem solved.
 >
 >
 > Arild S wrote:
 > > On Thu, 31 Aug 2006 19:29:20 -0700, auto208611@HUSHMAIL.COM wrote:
 > >
 > > >Is there a method within this Proc Sql that I can get
 > > >the creation of a macro list to have to have a single
 > > >quote on both sides of the comma, so that final
 > > >macro string resluts in this string:
 > > >
 > > >'01234','43210','56789','99999'
 > > >
 > > >proc sql noprint ;
 > > >select zipnum into :zips separated by ',' from dataset1;
 > > >
 > > >Thanks
 > >
 > >
 > > proc sql noprint;
 > >  select "'"||trim(name)||"'" into :list separated by ','
 > >  from sashelp.class;
 > > quit;
 > > %put &list;
 > >
 > >
 > > and
 > >
 > > select quote(trim(name))  will give doubles..
 > >
 > >
 > > Rgds, A
 >
 >