Date:         Fri, 13 Apr 2007 08:04:03 -0700
Reply-To:     Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Jack Hamilton <jfh@STANFORDALUMNI.ORG>
Subject:      Re: Single quotes around CSV
Comments: To: vldesilva@GMAIL.COM
In-Reply-To:  <1176475292.530726.256780@e65g2000hsc.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

You would have gotten a better answer sooner if you had given us this
context earlier.

Given that you're creating the macro string with SAS, why not just
create it correct in the first place?

  proc sql;
     select distinct "'" || provno || "'"
       into :entered separated by ','
       from final;
  quit;

If the PROVNO variable is character, you might want to put it inside a
TRIM function; if it's numeric you might need to add a PUT function.

If the remote database will accept strings in double quotes, you can use
the QUOTE function instead of concatenation operators.  SAS really ought
to enhance that function to allow a quote character of your choice.

In version 9, you'd be better off using the CATX function, which does an
implicit PUT and TRIM.



vldesilva@GMAIL.COM wrote:
> Thanks for all the input guys.
>
> This is the code I was using
>
> proc sql;
>    select distinct provno
>      into :entered separated by ','
>      from final;
> quit;
>
> Then I wanted to use this list to pull som other data as below:
>
> proc sql;
>      connect to odbc as DW (dsn="&dsnname" uid=blah pwd=blah);
>      create table cnhmas as
>      select *
>      from connection to DW (
>                 select *
>                 from &dsnname..CNHMAS where provno
>         in (&provlist));
> quit;
>
> instead of &provlist I tried to put the sql statement but that didn't
> work as the tables were from different databases.
>
> I tried some of the suggestions already like, separating by ',' but
> that didn't work. I will try some of the other suggestions and let
> y'all know how it goes. Thanks again.