topic Re: PROC SQL LIKE operator to combine rows across tables with colums with similar values in SAS Programming

PROC SQL LIKE operator to combine rows across tables with colums with similar values

Varrelle — Sun, 19 Dec 2021 21:36:17 GMT

Hello SAS community:

I have one table named 'table1' with a column named 'X1' and a second table, 'table2', with a column named 'X2'. I want to combine a row from 'table1' and 'table2' when the value of 'X1' from 'table1' is a 'close' match to the value of 'X2' in 'table2'.

For example, if the value of 'X1' from a row in 'table1' is the string 'abc.&.123', and the value of 'X2' from a row in 'table2' is 'abc_and_123', then I would like to return a row which is the combined rows from 'table1' and 'table2' on this 'close' match between variables 'X1' from 'table1' and 'X2' from 'table2'.

I can use the LIKE operator to locate each row from each table as follows:

/*FOR table1:*/ 
PROC SQL; SELECT X1 
FROM table1 
WHERE X1 LIKE 'abc%123'; 
QUIT; 

/*FOR table2:*/ 
PROC SQL; 
SELECT X2 
FROM table2 
WHERE X2 LIKE 'abc%123'; 
QUIT;

Basically I would like to do a JOIN for the two tables on a 'close' match. I wonder if the LIKE operator can achieve this, or perhaps there is another strategy that would allow me to do the same?

Thanks.

Re: PROC SQL LIKE operator to combine rows across tables with colums with similar values

PaigeMiller — Sun, 19 Dec 2021 23:01:57 GMT

Search this forum (and google the rest of the internet) for "fuzzy matching".

Re: PROC SQL LIKE operator to combine rows across tables with colums with similar values

SASKiwi — Mon, 20 Dec 2021 01:40:26 GMT

You haven't posted much detail on the types of fuzzy matching you want. The only example you have given looks like it is made-up. It would be helpful if you could provide more examples. Typically DATA steps are much better for fuzzy matching.

Re: PROC SQL LIKE operator to combine rows across tables with colums with similar values

Varrelle — Wed, 22 Dec 2021 02:37:42 GMT

thanks for the tip @PaigeMiller

Re: PROC SQL LIKE operator to combine rows across tables with colums with similar values

Varrelle — Wed, 22 Dec 2021 02:38:52 GMT

thanks for your comment @SASKiwi

Re: PROC SQL LIKE operator to combine rows across tables with colums with similar values

Varrelle — Wed, 22 Dec 2021 02:41:32 GMT

this is a link to a paper that seems to have solved my problem. thanks again to @PaigeMiller and @SASKiwi for their helpful feedback and commentary.

NESUG 2011 -- PROC SQL fuzzy matching

Re: PROC SQL LIKE operator to combine rows across tables with colums with similar values

SASKiwi — Wed, 22 Dec 2021 20:39:25 GMT

@Varrelle - No problem, glad to hear you have figured it out for yourself. Please bear in mind that using a range of techniques is best for fuzzy matching. Start by doing exact matches, then use fuzzy techniques on the remaining data.