This tutorial explains how to use the EXCEPT operator in PROC SQL in SAS, along with examples.
Create Sample Datasets
The following SAS program creates datasets that will be used to explain examples in this tutorial.
data datasetA; input product $ sales; cards; A 60 A 60 A 65 B 36 C 78 D 91 ; run; data datasetB; input product $ profit; cards; A 60 A 62 D 23 E 25 F 32 G 41 ; run;
EXCEPT Operator
The EXCEPT operator within PROC SQL is used to find unique rows from the first table that are not found in the second table.
proc sql; create table outData as select * from datasetA EXCEPT select * from datasetB; quit;
The EXCEPT Operator does not keep duplicate records.
EXCEPT ALL
The ALL keyword keeps the duplicate records.
proc sql; create table outData as select * from datasetA EXCEPT ALL select * from datasetB; quit;
You may have noticed that EXCEPT ALL returned 5 rows in the output dataset, whereas the EXCEPT operator returned 4 rows.
EXCEPT CORR
The EXCEPT CORR keyword tells SAS to find matches based on common column in the tables. It returns unique rows in the first table that do not appear in the second table based on common column.
proc sql; create table outData as select * from datasetA EXCEPT CORR select * from datasetB; quit;
The EXCEPT CORR returned only one column as column 'Product' was the only common column in both the datasets.
EXCEPT ALL CORR
The EXCEPT ALL CORR is almost similar to EXCEPT CORR but it does not remove duplicate rows.
proc sql; create table outData as select * from datasetA EXCEPT ALL CORR select * from datasetB; quit;
Share Share Tweet