Querying Multiple Tables (1)

Performing Queries Using SQL

Querying Multiple Tables

This topic deals with the more complex task of extracting data from two or more tables.

In the previous practice, you wrote a PROC SQL step to query a single table. Suppose you now want to examine data stored in two tables. In SQL terminology, this is called joining tables.

Suppose you want to join tables containing employee information such as name, phone, and room number. For your query, you want to select and create columns. You also need to be sure that the rows you join belong to the same employee. To check this, you want to match employee identification numbers for rows you merge.

Insure, Clinic and Query boxes

Now let's see how you 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 joins data from the tables Payroll and BldgInfo, which are both stored in a SAS library that is assigned the libref Emplib.

     proc sql;
        select bldginfo.id,lastname,building,room,extension
           from emplib.payroll,emplib.bldginfo
           where bldginfo.id=payroll.id
           order by lastname;