Understanding Joins :: SAS(R) Studio 3.5: User's Guide

Joining Tables

When you create a query, you can join multiple tables together. SAS Studio can automatically join the tables together for you, or you can manually create the join. SAS Studio attempts to join tables by columns that have the same name and type. If no matches for column name and type are found, then you can specify the join criteria.

Note: If you have more than one table in your query and you do not specify join criteria, then your output data includes the Cartesian product, or every possible combination, of the data values.

Creating a Join

To add a table and automatically create a join:

From the Libraries of the navigation pane, drag the table that you want to add to the query to the Tables tab. Next, drop that table on top of the first table in the query to join the two.

Query Window with the Classfit Table Dragged from the Libraries Pane to the Class Table

The Join window displays the join criteria. In the following example, the Classfit table is automatically joined to the Class table by using the Name column in both tables.

Query Window with the Class and Classfit Tables Joined on the Name Column

If a join cannot be created automatically, you can specify the join condition manually.

To manually create a join:

On the Tables tab of the query window, make sure that you can view the tables that you want to join.
Click on the toolbar and select Join. The New Join window opens.
From the Left table drop-down list, select the table for the left side of the join.
From the Join type drop-down list, select the type of join that you want to use. The default join type is Inner join.
From the Right table drop-down list, select the table for the right side of the join.
Click Save. A join is created between the tables. If the tables include columns with matching names and data types, then a join condition is automatically created. If the tables do not include columns with matching names and data types, then you can select the columns for the join condition from the column drop-down lists.
To add another join condition to the join, click and select the columns that you want to use from the column drop-down lists.

Understanding the Types of Joins

SAS Studio supports four different types of joins. You can select the type of join you want by modifying an existing join.

You can select the join option that you want to use in the Join window.

SAS Studio Join Type	Join Icon	Description
Inner Join		The output rows include those for which the column in the first table matches the joining criterion of the column in the second table. Joins are inner joins by default.
Left Join		The output rows include all rows from the first table and the rows from the second table in which the joining criterion is met.
Right Join		The output rows include all rows from the second table and the rows from the first table in which the joining criterion is met.
Full Join		The output rows include all matching and nonmatching rows from both tables.

Modifying an Existing Join

You can modify an existing join by selecting a different type of join or by changing the columns that are used in the join condition. You can also add and remove join conditions or remove the entire join.

To modify a join:

On the Tables tab of the query window, click the join indicator that you want to modify. The join is displayed in the Join area.
To change the type of join, select a new type from the Join type drop-down list.
To add a new join condition, click and specify the columns to use in the join. To remove a join condition, click next to the appropriate condition.

To delete the entire join, right-click the join indicator and select Delete.