Adding Single Quote to list of strings

[GunnerEP]

Hi all,

I am stuck with a small issue in PROC SQL. I have to fetch from a flat file & use that string in a sql query but the items in the list are strings so during the fetch i need to fetch it with quotes to use it in oracle. Any Help would be much appretiated.

Thanks!

CODE.txt
AAA
AAB
AAC

filename colcode "/xyz/CODE.txt";run;

data col_code;
infile colcode;
length col_code $3.;
input col_code $;
run;

proc sql noprint;
select (trim(left(col_code))) into:collist separated by ', '

from col_code;run;
%put Colleage LIST IS: &collist ;

Current Output:-

Colleage LIST IS: AAA, AAB, AAC

Required Output:-

Colleage LIST IS: 'AAA', 'AAB', 'AAC'

I am using that &collist in a where condition in one sql run on oracle in later stages.!

[AhmedAl_Attar]

Hi,

You can do the following

proc sql noprint;
select " ' "||STRIP(col_code)||" ' " into:collist separated by ', '

from col_code;run;
%put Colleage LIST IS: %superq(collist);

In your Oracle query, use %unquote(&collist)  /* <--- This avoids getting SAS Macro compiler errors, yet preserves the single quotes around each value */

Hope this helps,

Ahmed

[GunnerEP]

Thanks ahmed for the reply!

The only trouble with this approach was

Colleage LIST IS: ' AAA ', ' AAB ', ' AAC ' // there were spaces in between.

[Reeza]

QUOTE() function. 

https://support.sas.com/documentation/cdl/en/lefunctionsref/67960/HTML/default/viewer.htm#p059exu866...

Proc SQL noprint;

select quote(name, "'") into :name_list separated by "," 

from sashelp.class;

quit;

%put &name_list;

[GunnerEP]

Thanks a lot.  🙂

[AhmedAl_Attar]

The space " ' " was left for code clarity purposes! otherwise it would have looked like this "'"!!

You can incorporate Reeza's quote(name,"'") function use instead in your first sql statement.

Ahmed

[Kurt_Bremser · Accepted solution]

Do it in a data step:

data _null_;
set col_code end=done;
length collist $1000; * set sufficient length here;
retain collist;
collist = catx(',',"'"!!col_code!!"'",trim(collist));
if done then call symput('collist',trim(collist));
run;

[GunnerEP]

Thanks a lot! Saved my day 🙂

[AhmedAl_Attar]

Here is another quick way.
1. Add the following to your existing code
%let collist = %sysfunc(tranwrd(%superq(collist)),%str(,),%str(%', %')));
%put Colleage LIST IS: %superq(collist) ;

[RW9]

A word of advice.  Using lists of data in macro language will almost always result in over complicated, hard to maintain or read code and processing.  Adding in quotation marks will really make your code even harder.  Also, in almost all cases there is not a need to do this at all anyways.  Simply changing the strcuture of the data or processing in a different manner results in far simpler code.

Now, you say you are using that macro variable in an SQL where clause later on, if so then just simply subquery the dataset in that where clause to avoid all this totally:

proc sql;
  create table WANT as
  select *
  from   HAVE
  where  ID in (select distinct COL_CODE from COL_CODE);
quit;

[data_null__]

You were almost there.

34         proc sql noprint;
35            select quote(strip(col_code),"'") into:collist separated by ', '
36            from col_code;
37            quit;
38            run;
39         %put Colleage LIST IS: &=collist ;
Colleage LIST IS: COLLIST='AAA', 'AAB', 'AAC'