topic Re: Multiple column in subquery in SAS Programming

Multiple column in subquery

Shantaram — Thu, 10 Aug 2023 12:20:40 GMT

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.

Re: Multiple column in subquery

PaigeMiller — Thu, 10 Aug 2023 14:59:40 GMT

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.

Re: Multiple column in subquery

ballardw — Thu, 10 Aug 2023 14:55:37 GMT

@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.

Re: Multiple column in subquery

Reeza — Thu, 10 Aug 2023 16:06:41 GMT

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;