The SQL Procedure

Example 5: Combining Two Tables

Procedure features:

DELETE statement

IS condition

RESET statement option

DOUBLE

UNION set operator

Tables:

PROCLIB.NEWPAY, PROCLIB.PAYLIST, PROCLIB.PAYLIST2

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

Input Tables

                           PROCLIB.PAYLIST Table

               Id
               Num   Gender  Jobcode    Salary    Birth    Hired
               -------------------------------------------------
               1639  F       TA1         42260  26JUN70  28JAN91
               1065  M       ME3         38090  26JAN54  07JAN92
               1400  M       ME1         29769  05NOV67  16OCT90
               1561  M                   36514  30NOV63  07OCT87
               1221  F       FA3             .  22SEP63  04OCT94

                             PROCLIB.PAYLIST2 Table

               Id
               Num   Gender  Jobcode    Salary    Birth    Hired
               -------------------------------------------------
               1919  M       TA2         34376  12SEP66  04JUN87
               1653  F       ME2         31896  15OCT64  09AUG92
               1350  F       FA3         36886  31AUG55  29JUL91
               1401  M       TA3         38822  13DEC55  17NOV93
               1499  M       ME1         23025  26APR74  07JUN92

Program

libname proclib 'SAS-library';

options nodate pageno=1 linesize=80 pagesize=60;

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;

Output: Listing

                                 Personnel Data

               Id
               Num   Gender  Jobcode    Salary    Birth    Hired
               -------------------------------------------------
               1065  M       ME3         38090  26JAN54  07JAN92

               1350  F       FA3         36886  31AUG55  29JUL91

               1400  M       ME1         29769  05NOV67  16OCT90

               1401  M       TA3         38822  13DEC55  17NOV93

               1499  M       ME1         23025  26APR74  07JUN92

               1639  F       TA1         42260  26JUN70  28JAN91

               1653  F       ME2         31896  15OCT64  09AUG92

               1919  M       TA2         34376  12SEP66  04JUN87

Top of Page