topic Identifying observations Found in Set B but not found in Set A in SAS Procedures

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

Benn — Fri, 11 Jan 2013 09:47:57 GMT

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!

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

Fraktalnisse — Fri, 11 Jan 2013 12:09:30 GMT

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;

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

SteveDenham — Fri, 11 Jan 2013 12:12:38 GMT

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

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

KarlK — Fri, 11 Jan 2013 14:29:58 GMT

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

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

joehinson — Fri, 11 Jan 2013 14:56:33 GMT

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;