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.

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;

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.