Finding differeing observations between two tables

[jcis7]

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!

[jhlaramore]

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...

[nehalsanghvi · Accepted solution]

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.

[jcis7]

Thank you everyone :)!