If you want a Rolls-Royce solution, there are some good commercial packages out there. But you can get a long way with a little bit of SQL and some knowledge of SOUNDEX and edit distance functions (COMPLEV, COMPGED).
SOUNDEX converts a character string to an expression that gives a rough idea of what it sounds like: vowels are omitted and similar-sounding consonants are lumped together. COMPLEV tells you how many single-character edits it takes to convert one string into another.
For instance, SOUNDEX("John") = J5. SOUNDEX("Johann") also equals J5, and SOUNDEX("Susan") = S25. Using COMPLEV to compare the SOUNDEX values tells us that "John" and "Johann" are very similar (score 0), but "John" and "Susan" are less similar (score 2).
COMPGED is a more sophisticated version of COMPLEV that accounts for the sorts of errors that are most commonly made: e.g. "Simon"->"Simmon" is more likely than "Simon"->"Simkon".
You can use these functions (and others of your choice) to generate a score for possible matches. Each pair of observations ends up with a score, and you use a cutoff to determine which ones should be considered as possible matches. Here's an example:
%let soundweight=20;
%let gedisweight=0.1;
%let cutoff=75;
data have;
id=_n_;
input firstname $ lastname $;
firstname=upcase(firstname); /* COMPGED is case-sensitive */
lastname=upcase(lastname);
firstnamesound=soundex(firstname);
lastnamesound=soundex(lastname);
cards;
Susan Smith
John Smith
Sue Smith
Johann Schmidt
Sue Jones
Sam Snell
Joe Johnson
Jae Johnston
;
run;
proc sql;
create table want as select
a.id as id1, b.id as id2,
a.firstname as firstname1, b.firstname as firstname2,
a.lastname as lastname1, b.lastname as lastname2,
a.firstnamesound as firstnamesound1, b.firstnamesound as firstnamesound2,
/* The next few variables don't need to be here - they are recalculated separately in the join condition below.
I've included them here so you can see what these intermediate functions look like before they're combined to
generate the overall match score. */
complev(a.firstnamesound,b.firstnamesound) as firstnamesoundscore,
complev(a.lastnamesound,b.lastnamesound) as lastnamesoundscore,
compged(a.firstname,b.firstname) as firstnameeditscore,
compged(a.lastname,b.lastname) as lastnameeditscore,
&soundweight*(calculated firstnamesoundscore + calculated lastnamesoundscore)
+&gedisweight*(calculated firstnameeditscore + calculated lastnameeditscore) as matchscore
from have as a inner join have as b on (a.id < b.id /* prevents duplicates and self-matches */ AND
(
&soundweight*(complev(a.firstnamesound,b.firstnamesound)+complev(a.lastnamesound,b.lastnamesound))
+&gedisweight*(compged(a.firstname,b.firstname)+compged(a.lastname,b.lastname))
)
<&cutoff);
quit;
You may want to play around with the weights and the scoring function, especially if you have other data that could be used to enhance the match. Raising the cutoff will increase the likelihood of accepting a match, so you'll get more false positives but fewer false negatives. Lowering it has the reverse effect.
