topic Re: Compare two data sets and keep only the records not in both in SAS Programming

Compare two data sets and keep only the records not in both

Paul_NYS — Tue, 23 Apr 2013 18:34:42 GMT

I have two data sets, one of which is a sub-set of the other. What I would like to do is to create a third data set that contains the records that are not common to both data sets. Does anyone know of a clean way to do this?

Paul

Re: Compare two data sets and keep only the records not in both

PGStats — Tue, 23 Apr 2013 18:43:27 GMT

If dataset B is a subset of dataset A then

proc sql;

create table A_minus_B as

select * from A

EXCEPT

select * from B;

quit;

selects rows from A which do not appear in B

Re: Compare two data sets and keep only the records not in both

Ron_MacroMaven — Tue, 23 Apr 2013 18:44:26 GMT

This macro provides one way to do that

http://www.sascommunity.org/wiki/Macro_Extract

Re: Compare two data sets and keep only the records not in both

Paul_NYS — Tue, 23 Apr 2013 18:54:52 GMT

This statement would not work because it does not give an indicator of a column to match on? Even though the records are a sub-set, the columns are slightly different between the two.

Paul

Re: Compare two data sets and keep only the records not in both

PGStats — Tue, 23 Apr 2013 18:59:02 GMT

Please look up documentation for SQL set operators in

SAS(R) 9.3 SQL Procedure User's Guide

Re: Compare two data sets and keep only the records not in both

Paul_NYS — Tue, 23 Apr 2013 19:06:17 GMT

I found an answer actually.

Paul

Re: Compare two data sets and keep only the records not in both

PGStats — Tue, 23 Apr 2013 19:21:32 GMT

I would like to know what it is. Could you please share? - PG

Re: Compare two data sets and keep only the records not in both

Ron_MacroMaven — Wed, 24 Apr 2013 11:34:50 GMT

Re: Compare two data sets and keep only the records not in both

data_null__ — Wed, 24 Apr 2013 12:18:37 GMT

Is it this last example from the page you cited.

Producing Rows from the First Query or the Second Query

There is no keyword in PROC SQL that returns unique rows from the first and second table, but not rows that occur in both. Here is one way that you can simulate this operation:

(query1 except query2) union (query2 except query1)

This example shows how to use this operation.

proc sql; title 'A EXCLUSIVE UNION B'; (select * from sql.a except select * from sql.b) union (select * from sql.b except select * from sql.a);

Producing Rows from the First Query or the Second Query

The first EXCEPT returns one unique row from the first table (table A) only. The second EXCEPT returns one unique row from the second table (table B) only. The middle UNION combines the two results. Thus, this query returns the row from the first table that is not in the second table, as well as the row from the second table that is not in the first table.