如何在Base SAS（PROC SQL）中实现两个数据集的差集运算

Got it, let's walk through how to pull the records that exist in your have dataset but not in havenot using PROC SQL in Base SAS.

First, a quick recap of your setup: you sorted have and created havenot to keep only the first record per party_ID. Now you want to extract all the remaining entries from have—the ones that didn't make it into havenot.

Method 1: Use the EXCEPT Operator (Clean & Straightforward) #

SAS PROC SQL supports the EXCEPT operator (available in SAS 9.2 and later) which directly returns rows present in the first query but missing from the second. This is perfect for your use case:

proc sql;
    create table diff as
    select party_ID, Preference_ID
    from have
    except
    select party_ID, Preference_ID
    from havenot;
quit;

Key Notes:

• The columns in both SELECT statements must match in count, order, and data type (which they do here, since havenot is derived directly from have).
• EXCEPT automatically removes duplicate rows (though your datasets don't have duplicates, so this doesn't affect your result).
• The output diff table will be sorted by the columns in your SELECT clause, matching the sorted order of your original have dataset.

Method 2: Use a LEFT JOIN with a Null Check (Compatible with Older SAS Versions) #

If you need more control over output order or are working with an older SAS version, a LEFT JOIN approach works reliably. We'll join have to havenot on both key columns, then filter out rows that had a match in havenot:

proc sql;
    create table diff as
    select h.party_ID, h.Preference_ID
    from have h
    left join havenot hn
        on h.party_ID = hn.party_ID
        and h.Preference_ID = hn.Preference_ID
    where hn.party_ID is null;
quit;

Key Notes:

• We use aliases h (for have) and hn (for havenot) to make the code easier to read.
• The ON clause ensures we only match rows where both party_ID and Preference_ID are identical (these are the unique identifiers for your records).
• The WHERE clause picks out rows from have that had no matching entry in havenot—indicated by hn.party_ID being null.

Expected Output #

Either method will produce your desired result:

party_ID Preference_ID
101 Preference2
102 Preference4
102 Preference5

内容的提问来源于stack exchange，提问作者SANCHIT SHARMA