How do I perform an outer join in SAS?

---

You can use the following basic syntax to perform an outer join with two datasets in SAS:

proc sql;
    create table final_table as
    select coalesce(x.team, y.team) as team, x.team, x.points, y.team, y.assists
    from data1 as x full join data2 as y
    on x.team = y.team;
quit;

This particular example performs an outer join using the full join statement and returns all rows from the datasets called data1 and data2.

The following example shows how to use this syntax in practice.

Example: Perform an Outer Join in SAS

Suppose we have the following two datasets in SAS that contain information about various basketball teams:

/*create datasets*/
data data1;
    input team $ points;
    datalines;
A 18
B 22
C 19
D 14
E 14
F 11
G 20
H 28 
;
run;

data data2;
    input team $ assists;
    datalines;
A 4
B 9
C 14
D 13
L 10
M 8
;
run;

/*view datasets*/
proc print data=data1;
proc print data=data2;

We can use the following syntax to perform an outer join and create a new dataset that contains each row from both datasets:

/*perform outer join*/
proc sql;
    create table final_table as
    select coalesce(x.team, y.team) as team, x.team, x.points, y.team, y.assists
    from data1 as x full join data2 as y
    on x.team = y.team;
quit;

/*view results of outer join*/
proc print data=final_table;

The resulting dataset contains every row from each individual dataset.

Note that we had to use the function to ensure that the team names from both datasets were returned in the resulting dataset.

If we didn’t use this function, then only the values from the team column in the first dataset would be displayed in the resulting dataset.

The following tutorials explain how to perform other common tasks in SAS: