How to find out the difference between two tables?

[ybz12003]

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.

[novinosrin]

Have you tried proc compare?

Also post a small sample and your comparison needs please

[ybz12003]

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

[novinosrin]

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?

[ybz12003]

Yes.

[novinosrin]

/*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;

[ballardw · Accepted solution]

Proc sql;

   create table different as

   select * from the biggerdataset

   except

   select * from the smallerdataset

   ;

quit;

may give you one place to start.

[ybz12003]

Awesome, Ballardw!  It works!

[henrykira]

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?

[bijay]

As mentioned in the above solution, use except with proc sql, and add union to concatenate the outcome.

data test1;
input pkey $ var1 var2 var3;
datalines;
a 1 2 3
b 4 5 5
;
run;

data test2;
input pkey $ var1 var2 var3;
datalines;
a 1 2 3
b 4 5 6
;
run;

proc sql;
(select * from test1
except
select * from test2 )
union
(select * from test2
except
select * from test1)
;
quit;