topic Fuzzy Lookup? in SAS Enterprise Guide

Fuzzy Lookup?

RandyW — Tue, 22 May 2018 22:19:14 GMT

I am new to SAS EG and I am not familiar with the programming side, only the point and click interface.

I have one column titled 'Name', and another column titled 'Name and Address' (sample attached). I need to determine if the name is contained in the name and address column.

What would you suggest is the best way to perform this action? In the query builder I tried creating a computed column with the contains operator and a second attempt with the scan function (which I thought was closest to a fuzzy lookup) but those did not work for me.

Any thoughts or guidance is much appreciated! Thanks in advance!

Re: Fuzzy Lookup?

Reeza — Tue, 22 May 2018 22:28:56 GMT

This is not a fuzzy lookup, you're looking for FIND or INDEX to search for a particular string instead. This assumes that comparisons are along a single row. If you need to compare the name from row 2 with the address/name from Row 1 that's a different problem.

This would be a calculated/computed column with the query builder, under Advanced Calculations most likely. You can use the INDEX or FIND function within the computed column builder.

@RandyW wrote:

I am new to SAS EG and I am not familiar with the programming side, only the point and click interface.

I have one column titled 'Name', and another column titled 'Name and Address' (sample attached). I need to determine if the name is contained in the name and address column.

What would you suggest is the best way to perform this action? In the query builder I tried creating a computed column with the contains operator and a second attempt with the scan function (which I thought was closest to a fuzzy lookup) but those did not work for me.

Any thoughts or guidance is much appreciated! Thanks in advance!

Re: Fuzzy Lookup?

Astounding — Tue, 22 May 2018 23:02:17 GMT

Another issue that may apply is the presence of trailing blanks. It is possible that the first comparison locates the NAME, when the second one doesn't:

location = index(name_plus_address, strip(name));

location = index(name_plus_address, name);

The STRIP function removes leading and trailing blanks. The second statement (omitting STRIP), might be searching for a longer string that includes the trailing blanks ... probably less than ideal.

Re: Fuzzy Lookup?

SuryaKiran — Tue, 22 May 2018 23:56:16 GMT

If you have mixed case then use FIND(Name_address,Name,'i')

Re: Fuzzy Lookup?

RandyW — Wed, 23 May 2018 00:29:55 GMT

Thanks to all! The following worked best for my needs but the other suggestions were good options too!

location = index(name_plus_address, strip(name));