topic Re: Join operator sounds like to compare two strings in SAS Enterprise Guide

Join operator sounds like to compare two strings

anonymous_user — Fri, 20 May 2016 17:04:20 GMT

Hi guys

Need some help with a join for a large amount of data, almost 2 million registers on table 1 and 300.000 on table 2.

We need to compare two strings on the where clause, but with an operator other than “equal”, we need an operator that can match by sounds like. Unfortunately we don't have any other variable to compare besides NAME, and the same names are typed in different ways.

We don't expect results 100% right but something to guide us on the right direction.

For example:

PROC SQL;

CREATE TABLE WORK.WANT AS

SELECT DISTINCT t1.NAME t2.NAME

FROM WORK.HAVE_1 t1 INNER JOIN WORK.HAVE_2 t2 ON (t1.NAME sounds like t2.NAME);

QUIT;

Is there any operator or SAS function that can do this? t1.NAME has length of 201 and t1.NAME has length of 40.

Example of data that we want to match using this join:

t1.NAME = MICHAEL JOHN TIM

t2.NAME = MICHAEL J. TIM

Thanks and regards!

Re: Join operator sounds like to compare two strings

Reeza — Fri, 20 May 2016 18:03:47 GMT

http://www.the-link-king.com/

SAS code is available on website to help with linkage rules.

You're still doing a massive amount of comparisons so it takes time.

Re: Join operator sounds like to compare two strings

ballardw — Fri, 20 May 2016 20:20:12 GMT

First step: Identify any exact matches.

Second Remove them from the comparison set. That may greatly reduce what ever you do next.

Re: Join operator sounds like to compare two strings

PGStats — Sat, 21 May 2016 03:29:24 GMT

To expand on @ballardw's idea, look at this recent thread

https://communities.sas.com/t5/General-SAS-Programming/Fuzzy-String-Matching-with-inconsistent-data/td-p/270446

Re: Join operator sounds like to compare two strings

Ksharp — Sat, 21 May 2016 03:32:42 GMT

YES.
SQL operator   =*   is for sound like. or you should check other functions like SPEDIS(),COMGEN() ........
Due to big table, I suggest to split it many sub-table,and make a macro to go through all these sub-tables,and SET them back together.

Re: Join operator sounds like to compare two strings

anonymous_user — Sat, 21 May 2016 16:44:44 GMT

Thanks it worked fine.

Follow the code and result of a test that I did with some drivers names and operator =* (sounds like).

Thanks again guys!!

data a;
length id $5;
length name $20;
   input id $ 1-5 name & $ 6-20;
   datalines;
11111 DAVID COULTHARD
22222 FERNANDO ALONSO
33333 SEBASTIAN VETTEL
44444 MAX VERSTAPPEN
55555 JENSON BUTTON
;
run;

data b;
length id $5;
length name $20;
   input id $ 1-5 name & $ 6-20;
   datalines;
66666 DAVID COULTH
77777 FERNAN ALO
88888 SEBASTI VET
99999 MAX VERSTA
00000 KIMI RAIKKONEN
;
run;

PROC SQL;
   CREATE TABLE c AS 
   SELECT t1.name, 
          t2.name AS name1
      FROM a t1
           INNER JOIN b t2 ON (t1.name =* t2.name);
QUIT;