topic How to fuzzy match two variables from two datasets? in SAS Programming

How to fuzzy match two variables from two datasets?

yanshuai — Tue, 29 Jan 2019 22:52:55 GMT

Hello all,

I have two datasets.

DATA HAVE1;
    INPUT (NAME1) (:$8.);
    CARDS;
ERIC Stewart
Eri John
ERI Abe
Eric Mars
Eris
Eric MARSTIN

;
run;

DATA HAVE2;
    INPUT (NAME2) (:$8.);
    CARDS;
Eric Stewart
Eri Johnny
Eri Lee
Eric Swift
Eric
Eric Strong
;
run;

What I want is to fuzzy match the NAME1 with NAME2. If NAME2 is similar with NAME1, then keep NAME2. Otherwise, remove NAME2.

1. They don't need to be perfectly same. Mostly match would be fine.

2. Regardless of the case. uppercase and lowercase match can be less important. As long as they are the same letter would be OK.

Here is what I want

DATA WANT;
    INPUT (NAME2) (:$8.);
    CARDS;
Eric Stewart
Eri Johnny
;
run;

Would this be achieved through SQL (because I have more other variables to group by and control for)

I know some codes such as LIKE CATS. But it seems the code is wrong and keeps processing and never ends.

proc sql;
select
a.*, b.*
from have1 a,
have2 b
where a.name1 LIKE cats('%',b.name2,'%');
quit;

Thank you very much.

Stay warm.

Re: How to fuzzy match two variables from two datasets?

PGStats — Tue, 29 Jan 2019 23:38:38 GMT

You could use a spelling distance function such as COMPLEV:

proc sql;
select *
from have2
where exists(select * from have1 where complev(name1, have2.name2, 4) < 4);  
quit;

Re: How to fuzzy match two variables from two datasets?

yanshuai — Sun, 03 Feb 2019 22:39:12 GMT

Thank you very much.

I use your code and also look for previous posts with similar questions.

The final coding I figure out is like this

proc sql;
select *
from have1, have2
where compged(have1.name1, have2.name2, 2, 'INL') < 2;
quit;

I am using COMPEGD and it gives me fairly good result. It also runs very quickly though it still involves cartesian product.

Thank you very much.

Hope this can also be helpful to others.