topic Re: PROC SQL subqueries in SAS Programming

PROC SQL subqueries

mglogan — Sat, 25 May 2019 05:33:41 GMT

Hello!

I am learning PROC SQL in more detail.

For the given example subquery:

proc sql;
        select empid, lastname, firstname, city, state
           from sasuser.staffmaster
           where empid in
              (select empid
                 from sasuser.payrollmaster
                 where month(dateofbirth)=2);
quit ;

I wrote what I thought was the equivalent using Join:

proc sql;
	select 
	 	empid, lastname, firstname, city, state
	from 
		sassuer.staffmaster as a
		inner join sasuser.payrollmaster as b
		where month(dateofbirth)=2 
	on a.empid = b.empid ;
quit;

[1] Is the correct?

[2] if so, what is the benefit to using subqueries?

Thank you!

Re: PROC SQL subqueries

Patrick — Sat, 25 May 2019 06:34:44 GMT

@mglogan

Let's say you have in table sasuser.staffmaster one row with empid=1 and in table sasuser.payrollmaster five rows with empid=1

With your code:

The inner join will return 5 rows, the subquery will return 1 row ...also investigate what EXISTS can do for you.

Re: PROC SQL subqueries

PeterClemmensen — Sat, 25 May 2019 07:05:08 GMT

1) There are a few things wrong with your code.. EmpId is in both tables, so you have to reference one of them. Also, you have a typo in a libref.

proc sql;
	select 
	 	a.empid, 
      lastname, 
      firstname, 
      city, 
      state
	from 
		sasuser.staffmaster as a
	inner join sasuser.payrollmaster as b
		on month(dateofbirth)=2 
	and a.empid = b.empid ;
quit;

I think this will help you: When to Use Joins and Subqueries

Re: PROC SQL subqueries

VDD — Sat, 25 May 2019 14:16:09 GMT

the difference is control over what element populated the results table.

did b over write a?

do I want the value of a or b?