Help using Base SAS procedures

Correct concatenation into an SQL statement

Reply
Contributor GDA
Contributor
Posts: 21

Correct concatenation into an SQL statement

I'm just starting to learn SQL/PROC SQL so hopefully this is an easy question to answer. I want to take the values from one
SQL statement and use them in another. I am using an "INTO" statement because it's handy at creating a delimeter. However
when I need to use the "IN" statement in my where clause I need to concatenate the (' to the front and the ') to the end. I thought
it would be straightforward but I seem to get some sort of error no matter what I put in there.

My Example below
-----------------------------------------------------------------------------------------------
proc sql;
select ID INTO :ID_VAR SEPARATED BY ''',''' from DB_RECORD WHERE FAMILY = 'ProductX';
QUIT;
%put &ID_VAR;
-----------------------------------------------------------------------------------------------
this yields &ID_VAR as

ID1','ID2','ID3','ID4

I then want to be able to query another database table with the contents of the &ID_VAR concatenated
with (' on the front and ') on the back so that it works in my IN clause

-----------------------------------------------------------------------------------------------
proc sql;
select runtime, equipment from DB_PROCESS WHERE ID IN xxx;
quit;
-----------------------------------------------------------------------------------------------

Obviously i'm looking to create xxx. Maybe I'm going about this wrong but it seemed easy at the time.
Please help me as I'm a noob.
SAS Super FREQ
Posts: 8,864

Re: Correct concatenation into an SQL statement

Hi:
Check out the QUOTE function. It will put double quotes around your variable's value. I had this example sample that uses SASHELP.CLASS.

If you used the QUOTE function, then you wouldn't have to worry about the parentheses ... they really belong to the WHERE and the IN operator anyway. You don't have to concatenate them to the list of names (or in your case, IDs) because macro variable substitution will plop the list of quoted names between the parentheses in the WHERE clause.

cynthia
[pre]
proc sql noprint;
select quote(trim(name)) INTO :want_name SEPARATED BY ","
from sashelp.class
WHERE age ge 15;
QUIT;
%put &want_name;

ods listing;
title 'Use with PROC SQL';
proc sql;
select *
from sashelp.class
where name in (&want_name);
quit;

title 'Use with PROC PRINT';
proc print data=sashelp.class;
where name in (&want_name);
quit;

[/pre]
Super Contributor
Posts: 474

Re: Correct concatenation into an SQL statement

Or apply directly single quotes when resolving the macro:
[pre]
proc sql;
select runtime, equipment from DB_PROCESS
WHERE ID IN (%str(%')&WANT_NAME%str(%'));
quit;
[/pre]

Cheers from Portugal.

Daniel Santos @ www.cgd.pt
Contributor GDA
Contributor
Posts: 21

Re: Correct concatenation into an SQL statement

Thanks!. It's working. Please stay tuned for more easy questions from me in the future.
Ask a Question
Discussion stats
  • 3 replies
  • 126 views
  • 0 likes
  • 3 in conversation