This topic deals with the more complex
task of extracting data from two or more tables.
Previously, you learned
how to write a PROC SQL step to query a single table. Suppose you
now want to examine data that is stored in two tables. PROC SQL enables
you to combine tables horizontally, in other words, to combine rows
of data.
In SQL terminology, combining tables horizontally is
called joining tables. Joins do not alter the original tables.
Suppose you want to
create a report that displays the following information for employees
of a company: employee identification number, last name, original
salary, and new salary. There is no single table that contains all
of these columns, so you must join the two tables Sasuser.Salcomps
and Sasuser.Newsals. In your query, you want to select four columns,
two from the first table and two from the second table. You also need
to ensure that the rows that you join belong to the same employee.
To check this, you want to match employee identification numbers for
rows that you merge and to select only the rows that match.
This type
of join is known as an inner join. An inner join returns a result
set for all of the rows in a table that have one or more matching
rows in another table.
You can write a PROC
SQL step to combine tables. To join two tables for a query, you can
use a PROC SQL step such as the one below. This step uses the SELECT
statement to join data from the tables Salcomps and Newsals. Both
of these tables are stored in a SAS library to which the libref Sasuser
has been assigned.
proc sql;
select salcomps.empid,lastname,
newsals.salary,newsalary
from sasuser.salcomps,sasuser.newsals
where salcomps.empid=newsals.empid
order by lastname;
We examine each clause
of this PROC SQL step.