Desktop productivity for business analysts and programmers

Search for words in a string

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

Search for words in a string

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


Accepted Solutions
Solution
‎02-20-2018 12:31 PM
SAS Employee
Posts: 30

Re: Search for words in a string

Posted in reply to rodrichiez

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;

 

View solution in original post


All Replies
PROC Star
Posts: 1,334

Re: Search for words in a string

Posted in reply to rodrichiez

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

 

Tom

 

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

Solution
‎02-20-2018 12:31 PM
SAS Employee
Posts: 30

Re: Search for words in a string

Posted in reply to rodrichiez

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 310 views
  • 1 like
  • 3 in conversation