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.
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;
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.
@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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.