topic Re: Finding differeing observations between two tables in SAS Procedures

Finding differeing observations between two tables

jcis7 — Thu, 02 Mar 2017 20:05:46 GMT

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!

Re: Finding differeing observations between two tables

jhlaramore — Thu, 02 Mar 2017 20:10:03 GMT

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#n1nwxbchh5hpu1n1h28kmici2awd.htm

Re: Finding differeing observations between two tables

nehalsanghvi — Thu, 02 Mar 2017 20:18:23 GMT

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.

Re: Finding differeing observations between two tables

jcis7 — Thu, 02 Mar 2017 23:04:01 GMT

Thank you everyone :)!