SQL Procedure

Example 5: Combining Two Tables

Features:

DELETE statement

IS condition

RESET statement option : DOUBLE

UNION set operator

Table names:

PROCLIB.NEWPAY

PROCLIB.PAYLIST

PROCLIB.PAYLIST2

Input Tables

This example creates a new table, PROCLIB.NEWPAY, by concatenating two other tables: PROCLIB.PAYLIST and PROCLIB.PAYLIST2.

proc sql;
title 'PROCLIB.PAYLIST Table';
   select * from proclib.paylist;

PROCLIB.PAYLIST Table

proc sql;
title 'PROCLIB.PAYLIST2 Table';
  select * from proclib.PAYLIST2;
title;

PROCLIB.PAYLIST2 Table

Program

libname proclib 'SAS-library';

proc sql;
   create table proclib.newpay as
      select * from proclib.paylist
      union
      select * from proclib.paylist2;

delete
      from proclib.newpay
      where jobcode is missing or salary is missing;

reset double;

title 'Personnel Data';

select *
      from proclib.newpay;

Program Description

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

libname proclib 'SAS-library';

Create the PROCLIB.NEWPAY table.The SELECT clauses select all the columns from the tables that are listed in the FROM clauses. The UNION set operator concatenates the query results that are produced by the two SELECT clauses.

proc sql;
   create table proclib.newpay as
      select * from proclib.paylist
      union
      select * from proclib.paylist2;

Delete rows with missing Jobcode or Salary values.The DELETE statement deletes rows from PROCLIB.NEWPAY that satisfy the WHERE expression. The IS condition specifies rows that contain missing values in the Jobcode or Salary column.

delete
      from proclib.newpay
      where jobcode is missing or salary is missing;

Reset the PROC SQL environment and double-space the output.RESET changes the procedure environment without stopping and restarting PROC SQL. The DOUBLE option double-spaces the output. (The DOUBLE option has no effect on ODS output.)

reset double;

Specify the title.

title 'Personnel Data';

Display the entire PROCLIB.NEWPAY table.The SELECT clause selects all columns from the newly created table, PROCLIB.NEWPAY.

select *
      from proclib.newpay;

Output

Personnel Data