BookmarkSubscribeRSS Feed
GDA
Calcite | Level 5 GDA
Calcite | Level 5
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.
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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]
DanielSantos
Barite | Level 11
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
GDA
Calcite | Level 5 GDA
Calcite | Level 5
Thanks!. It's working. Please stay tuned for more easy questions from me in the future.

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
  • 3 replies
  • 696 views
  • 0 likes
  • 3 in conversation