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
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.