How to Perform an Outer Join in SAS (With Example)

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;