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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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