topic Compare observations in 2 Datasets in SAS Procedures

Compare observations in 2 Datasets

Zakharkou — Tue, 24 Jan 2023 19:12:30 GMT

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.

Re: Compare observations in 2 Datasets

Quentin — Tue, 24 Jan 2023 19:19:02 GMT

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?

Re: Compare observations in 2 Datasets

PaigeMiller — Tue, 24 Jan 2023 19:19:43 GMT

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

Re: Compare observations in 2 Datasets

Kurt_Bremser — Tue, 24 Jan 2023 19:33:35 GMT

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;

Re: Compare observations in 2 Datasets

Reeza — Tue, 24 Jan 2023 19:34:17 GMT

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.

Re: Compare observations in 2 Datasets

Zakharkou — Tue, 24 Jan 2023 20:40:27 GMT

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

Re: Compare observations in 2 Datasets

Zakharkou — Tue, 24 Jan 2023 20:42:58 GMT

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

Re: Compare observations in 2 Datasets

Quentin — Tue, 24 Jan 2023 20:49:41 GMT

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