SQL Procedure

Example 4: Joining Two Tables

Features:

FROM clause : table alias

inner join

joined-table component

PROC SQL statement option : NUMBER

WHERE clause : IN condition

Table names:

PROCLIB.STAFF

PROCLIB.PAYROLL

Details

This example joins two tables in order to get more information about data that are common to both tables.

proc sql outobs=10;
   title 'PROCLIB.STAFF';
   title2 'First 10 Rows Only';
   select * from proclib.staff;
   title;

PROCLIB.STAFF Table

proc sql outobs=10;
   title 'PROCLIB.PAYROLL';
   title2 'First 10 Rows Only';
   select * from proclib.payroll;
   title;

PROCLIB.PAYROLL Table

Program

libname proclib 'SAS-library';

proc sql number;

title 'Information for Certain Employees Only';

/* Select the columns to display. The SELECT clause selects the columns to
show in the output. */
    select Lname, Fname, City, State,
           IdNumber, Salary, Jobcode

from proclib.staff, proclib.payroll

where idnumber=idnum and idnum in
              ('1919', '1400', '1350', '1333');

Program Description

Declare the PROCLIB library. The PROCLIB library is used in these examples to store created tables.

libname proclib 'SAS-library';

Add row numbers to PROC SQL output. NUMBER adds a column that contains the row number.

proc sql number;

Specify the title.

title 'Information for Certain Employees Only';

/* Select the columns to display. The SELECT clause selects the columns to
show in the output. */
    select Lname, Fname, City, State,
           IdNumber, Salary, Jobcode

Specify the tables from which to obtain the data. The FROM clause lists the tables to select from.

from proclib.staff, proclib.payroll

Specify the join criterion and subset the query. The WHERE clause specifies that the tables are joined on the ID number from each table. WHERE also further subsets the query with the IN condition, which returns rows for only four employees.

where idnumber=idnum and idnum in
              ('1919', '1400', '1350', '1333');

Information for Certain Employees Only