Date:         Tue, 23 Dec 2003 09:22:57 -0500
Reply-To:     "Fehd, Ronald J. (PHPPO)" <rjf2@CDC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Fehd, Ronald J. (PHPPO)" <rjf2@CDC.GOV>
Subject:      Re: Problem Using  %STR function to add special characters to a
              string
Comments: cc: Phillip Story <storyp@NASD.COM>
Content-Type: text/plain; charset="us-ascii"

> From: Phillip Story [mailto:storyp@NASD.COM]
> I am trying to create the following string
> from a column in a dataset ('AAA','BBB','CCC') in order to
> pass it to an Oracle query.
>
> E.g., Dataset:
> AAA
> BBB
> CCC

> From: Philip_Crane@WORKCOVER.VIC.GOV.AU
>      1  proc sql;
>      2     select key_id into :key_lst seperated by "','"
>      3       from key_file
>      4            ;
>      5  quit;
> In your example key_lst would contain the string AAA','BBB','CCC

which then needs to be surrounded by sQuotes when referenced

I would recommend:
PROC Sql noprint;
         select "'" !! trim(key_id) !! "'"
         into :key_lst
         separated by ","
         from key_file
         ;quit;

%Put key_lst<&key_lst.>;

This generates the complete string you were asking for
'AAA','BBB','CCC'

where each instance of Key_Id  is surrounded by sQuotes
and   each item     of Key_Lst is delimited  by comma

Ron Fehd  the SQL into:macro maven  CDC Atlanta GA USA RJF2@cdc.gov

By using your intelligence
you can sometimes make your problems twice as complicated.
-- Ashleigh Brilliant