MATCH MERGE crucially depends on observation order, which SQL (to my knowledge) wholly ignores.
Now, I think it is possible to replicate the match merge using the SQL code below. But it must be preceded by a pair of data step, to put sequence numbers for every obs in BY group
/* Make datasets A and B such that:
ID=1 is one to one
ID=2 is one to many (2)
ID=3 is many (2) to one
ID=4 is none to many (2)
ID=5 is many (2) to none
ID=6 is many (3) to many (3) with identical freqs (N(a)=N(b)
ID=7 is many (3) to many (2) with N(a)>N(b)
ID=8 is many (2) to many (3) with N(a)<N(b) */
data a;
input id atext $7. @@;
datalines;
1 Line1.1
2 Line2.1
3 Line3.1 3 Line3.2
5 Line5.1 5 Line5.2
6 Line6.1 6 Line6.2 6 Line6.3
7 Line7.1 7 Line7.2 7 Line7.3
8 Line8.1 8 Line8.2
run;
data b;
input id btext $7. @@;
datalines;
1 Line1.1
2 Line2.1 2 Line2.2
3 Line3.1
4 Line4.1 4 Line4.2
6 Line6.1 6 Line6.2 6 Line6.3
7 Line7.1 7 Line7.2
8 Line8.1 8 Line8.2 8 Line8.3
run;
data viewa /view=viewa;
set a;
by id;
if first.id then _seq1=1;
else _seq1+1;
run;
data viewb/view=viewb;
set b;
by id;
if first.id then _seq2=1;
else _seq2+1;
run;
proc sql;
create table want_sql
as select coalesce(a.id,b.id) as id, atext,btext, _seq1, _seq2
from viewa as a full join viewb as b
on (a.id=b.id)
group by calculated id
having a._seq1=b._seq2
or a._seq1>max(b._seq2) and _seq2=max(b._seq2)
or b._seq2>max(a._seq1) and _seq1=max(a._seq1)
;
quit;
The strategy here is to do exact matches on the BY variable (ID) and the calculated _SEQ variable. Then, for whichever data set has a higher frequency for the BY-group-in-hand, match it to the maximum _SEQ is the "smaller" dataset.
I think that this is a way to show SQL programmers what a simple match-merge does.
You can test the results of the above via:
proc sort data=want_sql;
by id _seq1 _seq2;
run;
data want_merge;
merge a b;
by id;
run;
proc compare base=want_merge compare=want_sql (drop=_seq1 _seq2) notes noprint;
run;
The PROC COMPARE reports:
NOTE: No unequal values were found. All values compared are exactly equal.
NOTE: The data sets WORK.WANT_MERGE and WORK.WANT_SQL are equal in all respects.
NOTE: There were 18 observations read from the data set WORK.WANT_MERGE.
NOTE: There were 18 observations read from the data set WORK.WANT_SQL.
And while adding a third data set C to the match-merge approach is trivial
data want_merge;
merge a b c;
by id;
run;
I do not want to think about replicating it in a single SELECT expression
Click image to register for webinar
