BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GunnerEP
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

10 REPLIES 10
AhmedAl_Attar
Rhodochrosite | Level 12

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
Obsidian | Level 7

Thanks ahmed for the reply!

 

The only trouble with this approach was

 

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

Reeza
Super User

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
Obsidian | Level 7

Thanks a lot.  🙂

AhmedAl_Attar
Rhodochrosite | Level 12

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
Super User

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
Obsidian | Level 7

Thanks a lot! Saved my day 🙂

AhmedAl_Attar
Rhodochrosite | Level 12
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
Diamond | Level 26 RW9
Diamond | Level 26

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__
Jade | Level 19

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'

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 51112 views
  • 15 likes
  • 6 in conversation