How can I use Proc SQL to find all the records that only exist in one table but not the other?

Question

I'm trying to do this in Enterprise Guide, with a task, otherwise I would just use a data step.

In a data step, this would be:

data names;
 input name $;
 datalines;
  John
  Mary
  Sally
  Fred
  Paul
 ;
run;

data check;
 input name $;
 datalines;
  Mary
  Fred
 ;

Proc sort data=names; by name; run;
Proc sort data=check; by name; run;

Data work.not_in_check;
 merge names(in=n) check(in=c);
 by name;
 if n and not c;
run;

score 9 · Accepted Answer · answered Jan 20 '12 at 19:37

9

Here's one way. There are surely many others.

proc sql;
 create table not_in_check as
 select name
 from names
 where name not in (select name from check);
quit;

answered Jan 20 '12 at 19:37

itzy

11,275
15
63
96

score 7 · Answer 2 · edited Jan 23 '12 at 12:42

7

Another slight variation is:

proc sql;
create table not_in_check as select 
 a.* from names as a left join 
          check as b on
          a.name=b.name
          where b.name is null;
quit;

edited Jan 23 '12 at 12:42

Aliaksei Kliuchnikau

13,589
4
59
72

answered Jan 23 '12 at 12:41

Longfish

7,582
13
19

This is the method I ended up using in EG. In the Filter Data section, I used b.name is missing. It would be great to see an explanation about why when I want a.name to not be present in b, b.name is null(missing) makes that happen.... – Jay Corbett Jan 23 '12 at 19:03
1

It's because 2 columns are being read in, a.name and b.name. These are then matched and therefore if a name exists in a but not b then the b.name record will be missing. The left join with the is null where clause ensures you only keep the names in a but not b. The select a.* ensures it only outputs data from table a, even though it is reading in data from both tables. Hope this helps. – Longfish Jan 24 '12 at 08:58

score 1 · Answer 3 · answered May 07 '14 at 18:01

The following method is pretty simple way to get records present in one table and not in the other.

Created table new with records containing sex = M, the result after the query will be records with sex = F.

Example:

data new;
set sashelp.class;
where sex = 'M';
run;
proc sql;
create table new1 as
select * from sashelp.class
except all 
select * from new;
quit;

Will put the code to test on my actual datasets which are of around 100k obs and update the result.

P.S: I know the question has been asked answered and forgotten,I was looking for a way to do the above and couldn't find a direct answer any where. So, adding so that it may come handy. :)

My first answer also. :)

score 0 · Answer 4 · edited Sep 15 '13 at 19:11

0

proc sql;
 create table inNamesNotIncheck
 as
 select *
 from names n
 where not exists
 (select name
 from check c
 where n.name=c.name);
quit;

edited Sep 15 '13 at 19:11

Chris

8,527
10
34
51

answered Sep 15 '13 at 18:49

user2781854

11

Hi, your post has been flagged as "low quality", probably because it consists solely of code. You could massively improve your answer by providing an explanation of exactly how and why this answers the question? – Ben Sep 15 '13 at 19:13
I appreciate the addition of the code. It shows a different but viable approach to the already described problem. Thank-you. – Wes McClintick Sep 16 '14 at 15:03

How can I use Proc SQL to find all the records that only exist in one table but not the other?

4 Answers4

Linked