How to chose items that don't match

[gordononline]

Hi

The code below identifies id and state where there is a match in the combinations but how does one do the inverse and find the ids and state combinations from the first table that don't have a corresponding match in the second table?  I've tried where id = id and state not = state, tried a correlated subquery and a range of coding attempts that give me errors.  I have a work around that works at present (that relies on me knowing all available values, concatanating them and doing a case statement with a find function) but I'd like to be able to write this dynamically as there are a host of uses for this in my area.

Here's an example:

data test;

input @1 id 1. @3 state $3.;

datalines;

1 vic

1 nsw

2 nsw

2 qld

3 wa

;

run;

data pwd;

input @1 pwd 2. @4 id 1. @6 state $3.;

datalines;

11 1 nsw

12 1 vic

13 2 nsw

14 2 vic

15 3 wa

16 3 qld

17 3 sa

;

run;

proc sql;

select * from work.pwd as t1

inner join work.test as t2 on t1.id=t2.id and t1.state=t2.state;

quit;

This gives me all the matching combinations.  However what I'm after is:

id state

2 vic

3 qld

3 sa

(i.e. all non-matching combinations).

Not fussed if this is done in a data step or proc sql but would prefer sql as my team is mainly a sql shop.

Thanks

Gordon

[Shmuel]

Just add one step more using sql:

proc sql;
create table matched as          /* line added to your code */
select * from work.pwd as t1
inner join work.test as t2 on t1.id=t2.id and t1.state=t2.state;

select * from work.test as t1
where not exists 
(select * from work.matched as t2
  where t1.id=t2.id and t1.state=t2.state);
quit;

quit;

[gordononline]

Thanks but this doesn't seem to work as SAS proc sql doesn't have 'outer join' as a statement. Likely T-SQL allows this though.

Doug

[Shmuel]

I have edited my code

[Shmuel]

Sorry. better do it by a data step:

data test;
input @1 id 1. @3 state $3.;
datalines;
1 vic
1 nsw
2 nsw
2 qld
3 wa
;
run;

data pwd;
input @1 pwd 2. @4 id 1. @6 state $3.;
datalines;
11 1 nsw
12 1 vic
13 2 nsw
14 2 vic
15 3 wa
16 3 qld
17 3 sa
;
run;

proc sort data=test; by id state; run;
proc sort data=pwd;  by id state; run;
data want;
merge test (in=in1)
          pwd (in=in2);
 by id state;  
     if  not (in1 and in2);
run;

[Shmuel · Accepted solution]

You can create both matched and unmatched items in the same data step:

proc sort data=test; by id state; run;
proc sort data=pwd;  by id state; run;

data matched unmatched;
merge test (in=in1)
      pwd (in=in2);
 by id state;  
     if (in1 and in2) then output matched;
     else output unmatched;
run;