topic Correct concatenation into an SQL statement in SAS Procedures

Correct concatenation into an SQL statement

GDA — Wed, 20 Jan 2010 21:18:03 GMT

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.

Re: Correct concatenation into an SQL statement

Cynthia_sas — Thu, 21 Jan 2010 01:18:57 GMT

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]

Re: Correct concatenation into an SQL statement

DanielSantos — Thu, 21 Jan 2010 09:12:04 GMT

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

Re: Correct concatenation into an SQL statement

GDA — Thu, 21 Jan 2010 14:53:29 GMT

Thanks!. It's working. Please stay tuned for more easy questions from me in the future.