Search for words in a string

[rodrichiez]

Hello,

I want to know how could I search every word from a string in another string, I have 2 tables with names, but I need to search for coincidences, and bring the match found to another column. The result I'm looking for is a table where I can see the matching names from both tables.

TABLE 1:

JOSE MANUEL
YULISA PEREZ
JULIO CESAR VALENTIN
SHEILA DE LA ROSA
CHRISTIAN RODRIGUEZ

TABLE 2:
JOSE MANUEL
YULISA PEREZ
JULIO CESAR ALMANZAR
SUGELY DE LA ROSA
CHRISITAN QUEZADA

[TomKari]

Here's a similar, but not identical, situation. Hope it helps!

Tom

http://support.sas.com/resources/papers/proceedings14/1717-2014.pdf

[GinaRepole · Accepted solution]

If all you're looking for is to keep a single variable list of matching names, you can do something simple like this:

DATA work.matches;
	KEEP Name1;
	MERGE work.table1 work.table2;
	IF Name1 = Name2;
RUN;

If you're looking for something a little fancier like a fuzzy match where people with different first names but similar last names match as well, you can try a couple of approaches mentioned in this blog post. Here's a sample of one of the methods that you can run now, if you'd like to:

DATA work.table1;
	LENGTH Name1 $ 50;
	INFILE datalines DSD;
	INPUT Name1 $;
DATALINES;
JOSE MANUEL
YULISA PEREZ
JULIO CESAR VALENTIN
SHEILA DE LA ROSA
CHRISTIAN RODRIGUEZ
;
RUN;

DATA work.table2;
	LENGTH Name2 $ 50;
	INFILE datalines DSD;
	INPUT Name2 $;
DATALINES;
JOSE MANUEL
YULISA PEREZ
JULIO CESAR ALMANZAR
SUGELY DE LA ROSA
CHRISITAN QUEZADA
;
RUN;

DATA work.compare;
	MERGE work.table1 work.table2;
	result1 = SPEDIS(Name1, Name2);
	result2 = SPEDIS(Name2, Name1);
	result = MEAN(result1, result2);
RUN;

PROC PRINT;
RUN;

I created two tables with you data in them. Then used a MERGE to smash them together, and took advantage of the SPEDIS function to calculate the offset between the names. I swapped the order of the names, recalculated, and averaged just because the length of the first argument changes the final result. Averaging might keep it more consistent if the two names are vastly different lengths. This could still work if you're trying to find only full matches-- just look for any row whose result value = 0;