SAS: EXCEPT Operator in PROC SQL

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;

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn