BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Shantaram
Calcite | Level 5

proc sql;
create table test AS
select * from emp where (empid, name) in (select empid,name from emp_master);
quit;

 

same code is working in the sql server but not in the sas. 

How we can add multiple columns in the subquery.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Looks to be an inner join or you can use pass thru as indicated by @PaigeMiller 

 

proc sql;
create table test as
select e.* 
from emp as e
inner join emp_master as em
on e.empid=em.empid and e.name=em.name;
quit;

 

 

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

Always a good idea to check the documentation, which explains:

 

PROC SQL allows a subquery (contained in parentheses) at any point in an expression where a simple column value or constant can be used. In this case, a subquery must return a single value, that is, one row with only one column.

Probably, you can use the PASSTHRU feature in SAS PROC SQL to run native SQL Server code inside SAS PROC SQL.

--
Paige Miller
ballardw
Super User

@Shantaram wrote:

proc sql;
create table test AS
select * from emp where (empid, name) in (select empid,name from emp_master);
quit;

 

same code is working in the sql server but not in the sas. 

How we can add multiple columns in the subquery.


If possible then run the query on data in SQL server and then bring the result to SAS.

 

I'm not even sure exactly what your query is supposed to return (no data or example for instance). You might run an actual query and see if a join on that result will work such as on (and I am guessing here) ON t1.Empid=t2.Empid and t1.Name= t2.Name   where T1 and T2 are aliases for the tables the value come from. The general way that IN comparisons work in SAS anything that looks like (a, b) in (any list of values) is going to be a syntax error as far as I can tell. IN is looking for one value in a list of other values.

 

Do not expect syntax from different flavors of SQL to run as Proc SQL code as almost every implementation has extensions to "standard" SQL and Proc SQL doesn't have them. 

Reeza
Super User

Looks to be an inner join or you can use pass thru as indicated by @PaigeMiller 

 

proc sql;
create table test as
select e.* 
from emp as e
inner join emp_master as em
on e.empid=em.empid and e.name=em.name;
quit;

 

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 658 views
  • 1 like
  • 4 in conversation