Practical Problem-Solving with PROC SQL

Comparing Tables

You have two copies of a table. One of the copies has been updated. You want to see which rows have been changed.

There are two tables, the OLDSTAFF table and NEWSTAFF table. The NEWSTAFF table is a copy of OLDSTAFF. Changes have been made to NEWSTAFF. You want to find out what changes have been made.

Sample Input Tables for Table Comparison

                                Old Staff Table

          id        Last        First     Middle    Phone     Location
          ------------------------------------------------------------
          5463      Olsen       Mary      K.        661-0012  R2342   
          6574      Hogan       Terence   H.        661-3243  R4456   
          7896      Bridges     Georgina  W.        661-8897  S2988   
          4352      Anson       Sanford             661-4432  S3412   
          5674      Leach       Archie    G.        661-4328  S3533   
          7902      Wilson      Fran      R.        661-8332  R4454   
          0001      Singleton   Adam      O.        661-0980  R4457   
          9786      Thompson    Jack                661-6781  R2343

                                New Staff Table

          id        Last        First     Middle    Phone     Location
          ------------------------------------------------------------
          5463      Olsen       Mary      K.        661-0012  R2342   
          6574      Hogan       Terence   H.        661-3243  R4456   
          7896      Bridges     Georgina  W.        661-2231  S2987   
          4352      Anson       Sanford             661-4432  S3412   
          5674      Leach       Archie    G.        661-4328  S3533   
          7902      Wilson      Fran      R.        661-8332  R4454   
          0001      Singleton   Adam      O.        661-0980  R4457   
          9786      Thompson    John      C.        661-6781  R2343   
          2123      Chen        Bill      W.        661-8099  R4432

Solution

To display only the rows that have changed in the new version of the table, use the EXCEPT set operator between two SELECT statements.

proc sql;
   title 'Updated Rows';
   select * from newstaff 
   except
   select * from oldstaff;

Rows That Have Changed

                                  Updated Rows

          id        Last        First     Middle    Phone     Location
          ------------------------------------------------------------
          2123      Chen        Bill      W.        661-8099  R4432   
          7896      Bridges     Georgina  W.        661-2231  S2987   
          9786      Thompson    John      C.        661-6781  R2343

How It Works

The EXCEPT operator returns rows from the first query that are not part of the second query. In this example, the EXCEPT operator displays only the rows that have been added or changed in the NEWSTAFF table.

Note: Any rows that were deleted from OLDSTAFF will not appear. [cautionend]

Top of Page