## Finding differeing observations between two tables

Solved
Regular Contributor
Posts: 206

# Finding differeing observations between two tables

Hello!

I have two sas tables and am trying figure out which observations between the two tables are different.

I don't know if table a has observations table b doesn't have and vice versa.

How would I do that?  They differ by the identifying variable schcode

proc sql;

create table differing as

select a.*

from a

left join b on a.schcode=b.schcode;

quit;

Thanks!

Accepted Solutions
Solution
‎03-02-2017 06:03 PM
Frequent Contributor
Posts: 75

## Re: Finding differeing observations between two tables

Are you simply trying to see which observations are in one and and not in the other? If so, then this would work:

``````proc sql;
create table differing as
select *, 'In A, not in B' as Note
from a
where schcode not in (select schcode from b)
union
select *, 'In B, not in A' as Note
from b
where schcode not in (select schcode from a);
quit;``````

If you're trying to column compare values as well, then proc compare is the way to go.

All Replies
SAS Employee
Posts: 15

## Re: Finding differeing observations between two tables

Take a look at PROC COMPARE. It's an excellent option for comparing observations between two SAS data sets.

http://support.sas.com/documentation/cdl/en/proc/69850/HTML/default/viewer.htm#n1nwxbchh5hpu1n1h28km...

Solution
‎03-02-2017 06:03 PM
Frequent Contributor
Posts: 75

## Re: Finding differeing observations between two tables

Are you simply trying to see which observations are in one and and not in the other? If so, then this would work:

``````proc sql;
create table differing as
select *, 'In A, not in B' as Note
from a
where schcode not in (select schcode from b)
union
select *, 'In B, not in A' as Note
from b
where schcode not in (select schcode from a);
quit;``````

If you're trying to column compare values as well, then proc compare is the way to go.

Regular Contributor
Posts: 206