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!
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;
2)
I think this will help you: When to Use Joins and Subqueries
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.
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;
2)
I think this will help you: When to Use Joins and Subqueries
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?
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.