SAS/SQL Joins Examples

Dataset ankit1

data ankit1;
input name $ age emp_id  salary manager_id;
datalines;
ankit 20 1 20000 1
ankit1 21 2 30000 1
amit2 20 4 40000 2
amit 21 3 50000 3
anil 22 5 60000 3
ansu 23 6 70000 3
;
run;

Dataset ankit

data ankit;
input name $ age;
datalines;
ankit 23
ankit1 24
amit2 25
amit 25 
ans 12
ansu 13
;
run;

Inner JOIN

An inner join returns only the subset of rows from the first table that matches rows
from the second table.



proc sql;
select ankit1.name,ankit.age
from ankit1 inner join ankit 
on ankit1.name=ankit.name;
quit;

Outer Joins

Outer joins are inner joins that are augmented with rows from one table that do not

match any row from the other table in the join. The resulting output includes rows that

match and rows that do not match from the join’s source tables. Nonmatching rows

have null values in the columns from the unmatched table. Use the ON clause instead

of the WHERE clause to specify the column or columns on which you are joining the

tables. However, you can continue to use the WHERE clause to subset the query result.

Left JOIN

A left outer join lists matching rows and rows from the left-hand table (the first

table listed in the FROM clause) that do not match any row in the right-hand table. A

left join is specified with the keywords LEFT JOIN and ON.

proc sql;
select ankit1.name,ankit.age
from ankit1 left join ankit 
on ankit1.name=ankit.name;
quit; 

                                                                         Right JOIN

A right join, specified with the keywords RIGHT JOIN and ON, is the opposite of a

left join: nonmatching rows from the right-hand table (the second table listed in the

FROM clause) are included with all matching rows in the output.

proc sql;
select ankit1.name,ankit.age
from ankit1 right join ankit 
on ankit1.name=ankit.name;
quit;

Full JOIN

A full outer join, specified with the keywords FULL JOIN and ON, selects all
matching and nonmatching rows.



proc sql;
select ankit1.name,ankit.age
from ankit1 full join ankit 
on ankit1.name=ankit.name;
quit;