Help using Base SAS procedures

Finding differeing observations between two tables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 192
Accepted Solution

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.

View solution in original post


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: 192

Re: Finding differeing observations between two tables

Thank you everyone Smiley Happy!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 158 views
  • 1 like
  • 3 in conversation