Help using Base SAS procedures

Adding Single Quote to list of strings

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Adding Single Quote to list of strings

[ Edited ]

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


Accepted Solutions
Solution
‎10-05-2016 08:48 AM
Super User
Posts: 7,094

Re: Adding Single Quote to list of strings

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Regular Contributor
Posts: 213

Re: Adding Single Quote to list of strings

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

Contributor
Posts: 32

Re: Adding Single Quote to list of strings

Thanks ahmed for the reply!

 

The only trouble with this approach was

 

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

Super User
Posts: 18,529

Re: Adding Single Quote to list of strings

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;

Contributor
Posts: 32

Re: Adding Single Quote to list of strings

Thanks a lot.  Smiley Happy

Regular Contributor
Posts: 213

Re: Adding Single Quote to list of strings

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

Solution
‎10-05-2016 08:48 AM
Super User
Posts: 7,094

Re: Adding Single Quote to list of strings

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 32

Re: Adding Single Quote to list of strings

Thanks a lot! Saved my day Smiley Happy

Regular Contributor
Posts: 213

Re: Adding Single Quote to list of strings

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) ;
Super User
Super User
Posts: 7,565

Re: Adding Single Quote to list of strings

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;

 

Respected Advisor
Posts: 3,780

Re: Adding Single Quote to list of strings

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'
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 2517 views
  • 8 likes
  • 6 in conversation