## How to find out the difference between two tables?

Solved
Super Contributor
Posts: 393

# How to find out the difference between two tables?

Hello:

I have table A with 32 variables of 4286 observations and table B with 32 variables of 3996 observations.   All the variables of the two tables are the same.  How to find out the difference of 290 observations between A and B.

Thanks.

Accepted Solutions
Solution
‎01-24-2018 02:20 PM
Super User
Posts: 13,084

## Re: How to find out the difference between two tables?

Proc sql;

create table different as

select * from the biggerdataset

except

select * from the smallerdataset

;

quit;

may give you one place to start.

All Replies
PROC Star
Posts: 1,351

## Re: How to find out the difference between two tables?

Have you tried proc compare?

Also post a small sample and your comparison needs please

Super Contributor
Posts: 393

## Re: How to find out the difference between two tables?

I did.  But it seems the observations are too big.   It only listed the 1000.

PROC Star
Posts: 1,351

## Re: How to find out the difference between two tables?

is it something like

pkey var1 var2 var3

a        1      2      3

b        4      5      6

c         7      8      9

pkey var1 var2 var3

a        1      2      3

b        4      5      6

Pick the C?

Super Contributor
Posts: 393

## Re: How to find out the difference between two tables?

Posted in reply to novinosrin

Yes.

PROC Star
Posts: 1,351

## Re: How to find out the difference between two tables?

[ Edited ]
``````/*Really like that example?*/

data campare;
if _N_ = 1 then do;
if 0 then set tableB;/*smaller dataset*/
declare hash h(dataset: 'tableB');/*smaller dataset*/
h.defineKey('pkey');
h.defineData(all:'y');
h.defineDone();
end;
set tableA; /*the larger dataset*/
if h.check() ne 0 then output;
run;``````
Solution
‎01-24-2018 02:20 PM
Super User
Posts: 13,084

## Re: How to find out the difference between two tables?

Proc sql;

create table different as

select * from the biggerdataset

except

select * from the smallerdataset

;

quit;

may give you one place to start.

Super Contributor
Posts: 393

## Re: How to find out the difference between two tables?

Awesome, Ballardw!  It works!

Established User
Posts: 1

## Re: How to find out the difference between two tables?

Posted in reply to novinosrin

So what if it is something like:

pkey var1 var2 var3

a        1      2      3

b        4      5      5

pkey var1 var2 var3

a        1      2      3

b        4      5      6

pick b and display both?

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 8 replies
• 265 views
• 2 likes
• 4 in conversation