DATA Step, Macro, Functions and more

How to find out the difference between two tables?

Accepted Solution Solved
Reply
Super Contributor
Posts: 393
Accepted Solution

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.

View solution in original post


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