Identifying observations Found in Set B but not found in Set A

[Benn]

Hi Guys,

I currently have 2 sets of data: A and B,

and within each data set, there are 2 variables: Date, Key.

Basically, what I am trying to do is to identify the observations that are FOUND in B and NOT FOUND in A on a certain date with a certain key and then extract them into another set of data.

So for eg:

Set A                                                                   Set B

Date                            Key                                 Date

20080330                      1                                 20080330               1

20080330                      2                                 20080330               2

20080330                      3                                 20080330               4

20080401                      1                                 20080401               1

20080401                      2                                 20080401               2

20080401                      4                                 20080401               3

So in this case, Set C (which is the set I wish to create) will have the following observations:

Date                          Key

20080330                4              

20080401                3

The important thing to note that Dataset C should contain only observations that are FOUND in B but NOT FOUND in A. (Not the other way round or both ways round).

Many thanks guys!

[Fraktalnisse]

Hi!

It sounds like a Proc Sql join.

For example:

proc sql;

create table c as

select b.date,b.key

from a right join b

on a.date=b.date and

   a.key=b.key

where a.date=. and a.key=.;

quit;

[SteveDenham]

After a sort, a merge with in= option should give what you are looking for.

proc sort data=dataset_a;

by date key;

run;

proc sort data=dataset_b;

by date key;

run;

data dataset_c;

merge dataset_a (in=aa) dataset_b(in=bb);

by date key;

if bb and not aa;

run;

There is probably a proc sql approach that is better, but this is one that has worked across many versions of SAS.

Steve Denham

[KarlK · Accepted solution]

Made to order for the little-used "except" set operator:

proc sql;

create table c as

select * from b except select * from a;

quit;

Karl

[joehinson]

Without doubt, the best solution is Proc SQL with the EXCEPT set operator.

For a DATA step solution without the need for sorting, here is a simple hash solution (using the plural forms of the variable names):

data setc;

     declare hash a(dataset:"seta",ordered:"a");

     a.defineKey("dates","keys");

     a.defineDone();

     do until(done);

          set setb end=done;

          if a.check() ne 0 then output;

     end;

     stop;

run;