BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Paul_NYS
Obsidian | Level 7

Hi

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

1 ACCEPTED SOLUTION

Accepted Solutions
8 REPLIES 8
PGStats
Opal | Level 21

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

PG

PG
Paul_NYS
Obsidian | Level 7

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

PGStats
Opal | Level 21

Please look up documentation for SQL set operators in

SAS(R) 9.3 SQL Procedure User's Guide

PG

PG
Ron_MacroMaven
Lapis Lazuli | Level 10

see also

Using the EXCEPT Operator in PROC SQL

and Generation Data Sets to Produce a Comparison Report

Stanley Fogleman,

http://www.nesug.org/proceedings/nesug06/cc/cc10.pdf

Paul_NYS
Obsidian | Level 7

I found an answer actually.

Paul

PGStats
Opal | Level 21

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

PG
data_null__
Jade | Level 19

Is it this last example from the page you cited.

Producing Rows from the First Query or the Second Query

First or second table, but not both
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
A EXCLUSIVE UNION B
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.              

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3297 views
  • 4 likes
  • 4 in conversation