topic Re: Adding Single Quote to list of strings in SAS Procedures

Adding Single Quote to list of strings

GunnerEP — Wed, 05 Oct 2016 12:31:55 GMT

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.!

Re: Adding Single Quote to list of strings

AhmedAl_Attar — Wed, 05 Oct 2016 12:33:58 GMT

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

Re: Adding Single Quote to list of strings

Kurt_Bremser — Wed, 05 Oct 2016 12:36:40 GMT

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;

Re: Adding Single Quote to list of strings

AhmedAl_Attar — Wed, 05 Oct 2016 12:40:10 GMT

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) ;

Re: Adding Single Quote to list of strings

GunnerEP — Wed, 05 Oct 2016 12:47:00 GMT

Thanks ahmed for the reply!

The only trouble with this approach was

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

Re: Adding Single Quote to list of strings

GunnerEP — Wed, 05 Oct 2016 12:48:51 GMT

Thanks a lot! Saved my day 🙂

Re: Adding Single Quote to list of strings

Reeza — Wed, 05 Oct 2016 12:52:10 GMT

QUOTE() function.

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

Proc SQL noprint;

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

from sashelp.class;

quit;

%put &name_list;

Re: Adding Single Quote to list of strings

RW9 — Wed, 05 Oct 2016 13:02:10 GMT

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;

Re: Adding Single Quote to list of strings

GunnerEP — Wed, 05 Oct 2016 13:04:58 GMT

Thanks a lot. 🙂

Re: Adding Single Quote to list of strings

AhmedAl_Attar — Wed, 05 Oct 2016 13:14:20 GMT

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

Re: Adding Single Quote to list of strings

data_null__ — Wed, 05 Oct 2016 20:19:22 GMT

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'