BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
RandyW
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

4 REPLIES 4
Reeza
Super User

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!


 

 

Astounding
PROC Star

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.

RandyW
Calcite | Level 5

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));

SuryaKiran
Meteorite | Level 14

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

Thanks,
Suryakiran

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1907 views
  • 0 likes
  • 4 in conversation