Compare observations in 2 Datasets

[Zakharkou]

Hello dear SAS community.

I need help with the following issue.

There are 2 datasets named "one" and "two" with 696 and 695 observations respectively and same variable "id".

How to write out this one observation which is not present in "two" dataset?

For example with the following syntax id=x.

Many thanks in advance.

[Quentin]

As often happens with SAS, there are lots of ways to approach this.  PROC COMPARE comes to mind.  It could also be done with PROC SQL or a DATA step.

What code you have you tried? Are you comfortable with SQL or would your prefer a DATA step approach?

[Zakharkou]

I tried several times with proc compare, but that did not give the desired result.

[Quentin]

---

@Zakharkou wrote:
I tried several times with proc compare, but that did not give the desired result.

---

If you use an ID statement and the LISTOBS option, PROC COMPARE will tell which observations are missing.

data one ;
  set sashelp.class ;
run ;

data two ;
  set sashelp.class ;
  if _n_=3 then delete ;
run ;

proc compare base=one compare=two listobs ;
  id name ;
run ;

Returns:

                        The COMPARE Procedure
                 Comparison of WORK.ONE with WORK.TWO
                            (Method=EXACT)

                          Data Set Summary

      Dataset            Created          Modified  NVar    NObs

      WORK.ONE  24JAN23:15:47:23  24JAN23:15:47:23     5      19
      WORK.TWO  24JAN23:15:47:23  24JAN23:15:47:23     5      18


                          Variables Summary

                Number of Variables in Common: 5.
                Number of ID Variables: 1.

                 Comparison Results for Observations

   Observation 3 in WORK.ONE not found in WORK.TWO: Name=Barbara.

[PaigeMiller · Accepted solution]

proc sql;
    select a.id 
    from one as a left join two as b on a.id=b.id
    where missing(b.id);
quit;

[Kurt_Bremser]

proc sort data=one;
by id;
run;

proc sort data=two;
by id;
run;

data
  both
  only_one
  only_two
;
merge
  one (in=one keep=id)
  two (in=two keep=id)
;
by id;
if one and two
then output both;
else if one
then output only_one;
else output only_two;
run;

[Reeza]

proc sql;
create table want as
select * from one where id not in (select id from two);
quit;

---

@Zakharkou wrote:

Hello dear SAS community.

I need help with the following issue.

There are 2 datasets named "one" and "two" with 696 and 695 observations respectively and same variable "id".

How to write out this one observation which is not present in "two" dataset?

For example with the following syntax id=x.

Many thanks in advance.

---

[Zakharkou]

Thank you for the numerous responses. My problem was successfully solved.