Overview

By default, performing a left join on two tables results in a table that contains all the observations from the left table, along with the matching observations from the right table based on a common variable. The reverse is true when performing a right join on two tables. Often, however, you want your results to contain only the non-matching observations in one of the two tables. This sample explains how to retrieve only the non-matching rows from a join, and create an output table that contains only those rows.

This sample was created using SAS Enterprise Guide 4.2.

About the sample data

Following is code that you can use to create the data sets used in this sample. You can run these DATA steps in a SAS Enterprise Guide code node. To open a code node, select File ► New ► Program. The goal of this sample is to return all of the observations in the BASE table that are not in the RECORDS table, based on the common variable PATIENT.

data base;
input patient $ drug $ date: date9.;
format date date9.;
cards;
a    a    1jan2005
b    b    1feb2005
c    c    1mar2005
d    d    1mar2005
e    e    1apr2005
f    f    1may2005
g    g    1jun2005
h    h    1jun2005
;
run;

Figure 1. DATA step code for the BASE table

data records;
input patient $ drug2 $ date2: date9.;
format date date9.;
cards;
a    p    1aug2007
b    q    1nov2007
d    t    1oct2007
e    v    1nov2007
g    x    1dec2007
;
run;

Figure 2. DATA step code for the RECORDS table

Creating a table with only non-matching rows from a join of two tables

For illustration purposes, this sample uses a LEFT join.

1. Open the Query Builder. One way to do this is to click the Query Builder button above the input table data grid.

   

2. After the Query Builder window opens, click the Join Tables button to add the second table to the Query Builder.

   

3. In the Tables and Joins window, click the Add Tables button, then look in the Project for the RECORDS data set. Select the RECORDS data set and click Open to add it to the Tables and Joins window. By default, the join chosen is an inner join. Right-click on the join, select Properties, and choose Left Join.

   

   This is what the join should look like. The join is based on the common variable PATIENT.

   

4. Close the Tables and Joins window to return to the main Query Builder window. Select all the t1 (BASE) columns on the available columns side of the Query Builder and drag them onto the Select Data tab.

   

5. Create a filter on the query sd 'WHERE t1.patient NE t2.patient'. To do this, click on the Filter Data tab, and then the New Filter button. Choose Basic Filter and click Next. In the New Filter window, choose the PATIENT column from the t1(BASE) data set and click Next.

   

6. Change the operator to Not equal to. In the Value box, click the drop-down arrow and select the Columns tab. You will see a list of all columns in the query. Select t2.patient.

   

   This is what the filter should look like:

   

7. Click Finish and then run the query. The final data set should contain only the observations that are not in the RECORDS data set.

   

   Additional Documentation

   For more information about using SAS Enterprise Guide, see the SAS Enterprise Guide documentation page.

   
   
   

   ---

   These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Operating System and Release Information