topic Re: How to find out the difference between two tables? in SAS Programming

How to find out the difference between two tables?

ybz12003 — Mon, 22 Jan 2018 22:17:42 GMT

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.

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

novinosrin — Mon, 22 Jan 2018 22:20:03 GMT

Have you tried proc compare?

Also post a small sample and your comparison needs please

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

ybz12003 — Mon, 22 Jan 2018 22:23:10 GMT

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

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

novinosrin — Mon, 22 Jan 2018 22:27:15 GMT

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?

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

ybz12003 — Mon, 22 Jan 2018 22:30:15 GMT

Yes.

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

novinosrin — Mon, 22 Jan 2018 22:34:19 GMT

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

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

ballardw — Tue, 23 Jan 2018 00:32:01 GMT

Proc sql;

create table different as

select * from the biggerdataset

except

select * from the smallerdataset

;

quit;

may give you one place to start.

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

ybz12003 — Wed, 24 Jan 2018 19:20:31 GMT

Awesome, Ballardw! It works!

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

henrykira — Tue, 03 Apr 2018 15:14:41 GMT

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?

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

bijay — Fri, 01 Feb 2019 19:02:07 GMT

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;