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
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;
Here's a similar, but not identical, situation. Hope it helps!
Tom
http://support.sas.com/resources/papers/proceedings14/1717-2014.pdf
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.