SAS SQL Query Window User's Guide

Creating and Using Outer Joins

An outer join combines rows of data from two tables. There are three types of outer joins:

left join: returns all matching rows in both tables, in addition to rows in the left table that have no matching rows in the right table.
right join: returns all matching rows in both tables, in addition to rows in the right table that have no matching rows in the left table.
full join: returns all matching and unmatching rows from both tables.

In all three types of outer joins, the columns in the result row that are from the unmatched row are set to missing values.

In this example, you will first create an inner join that relates employee identification number and salary. Then, you will create an outer join that combines this data with data from another table to compute the gross monthly pay for employees who have taken leave.

Creating a Query View

You can create an SQL view that contains the syntax of your query. You will use this view to create an outer join query.

In the SQL QUERY TABLES window, select SAMPLE.EMPINFO and SAMPLE.SALARY from the list of Available Tables and add them to the list of Selected Tables. Select OK.

In the SQL QUERY COLUMNS window, select NAME, the two ADDRESS items, Identification Number, Employee number, Salary, BEGDATE, and ENDDATE and add them to the list of Selected Columns.

Select

View Where Conditions for Subset...

to display the WHERE EXPRESSION window.

Select EMPINFO.Identification Number from the list of Available Columns. Select EQ from the list of Operators. Select Salary.Identification Number from the list of Available Columns. Select OK.

This WHERE expression creates an inner join of EMPINFO and Salary based on Identification Number. To save the query as a view, select

Tools Show Query...

to display the SQL QUERY window. Select Create View.

[IMAGE]

Select the [→] next to the Library: field to display a list of SAS libraries.

[IMAGE]

The list of libraries displayed at your site may be different from the ones in the illustration. Select SAMPLE from the list of libraries. Select OK.

Type MYVIEW in the View: field. Select OK to return to the SQL QUERY window. Select Goback to return to the SQL QUERY COLUMNS window.

Creating an Outer Join

You can now create an outer join with other tables. Select

Tools Reset

Select OK in the pop-up dialog to reset the query.

Select SAMPLE.MYVIEW and SAMPLE.LEAVE from the list of Available Tables and add them to the list of Selected Tables. Select OK to display the SQL QUERY COLUMNS window.

Select

View Join Type

Select Matched Join and Unmatched rows (Outer Join). Select OK to display the Columns for Setting Join Criteria window.

[IMAGE]

Select Identification Number from SAMPLE.MYVIEW Columns (Left). Select Identification Number from SAMPLE.LEAVE Columns (Right). Select the arrow next to Join Type:. Select Left from the pop-up menu. Select OK to return to the SQL QUERY COLUMNS window.

Select

View Distinct

to eliminate duplicate values from your output.

Select NAME, Identification Number, and Employee Number from the list of Available Columns and add them to the list of Selected Columns.

[IMAGE]

Building a Column Expression

Select Build a Column to display the BUILD A COLUMN EXPRESSION window.

Select MYVIEW.Salary from the list of Available Columns. Select / from the list of Operators. Select <CONSTANT enter value> from the list of Available columns. Type 12 in the Numeric: field. Select OK. Click outside the list of operators to make it disappear.

Select Column Attributes to display the Expression Column Attributes window. Enter monthpay in the Alias Name= field. Enter dollar12.2 in the Format= field. Enter Employee's Monthly Pay in the Label= field.

[IMAGE]

Select OK to return to the BUILD A COLUMN EXPRESSION window. Select OK to return to the SQL QUERY COLUMNS window.

In the SQL QUERY COLUMNS window, select Build a Column to display the BUILD A COLUMN EXPRESSION window. Select Operators. Select ( from the list of Operators.

Select monthpay from the list of Available Columns. Select * from the list of Operators. Select LEAVE.Payroll percentage from the list of Available Columns. Select ) from the list of Operators. Click outside the list of operators to make it disappear.

[IMAGE]

Select Column Attributes to display the Expression Column Attributes window. Enter adjstpay in the Alias Name= field. Enter dollar12.2 in the Format= field. Enter Employee's Gross Pay in the Label= field. Select OK to return to the BUILD A COLUMN EXPRESSION window. Select OK to return to the SQL QUERY COLUMNS window.

Order By Columns

In the SQL QUERY COLUMNS window, select

View Order By...

to display the ORDER BY COLUMNS window

[IMAGE]

Select the second Identification Number from the list of Available Columns and add it to the list of Selected Columns. Select OK to return to the SQL QUERY COLUMNS window.

Viewing Your Output

Select

Tools Run Query Run Immediate

to display the results of the query in the OUTPUT window.

[IMAGE]

Chapter Contents
Previous
Next
Top of Page