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


Hi,

i'm working on DMS and creating webservice by two simple nodes (external data provider and sql lookup) and testing it. while testing i'm giving Name i/p field and providing name for ex as "MIKE" then its fetching exact match records from the table i,e all recs having MIKE only...but my requirement is to get all records sounds like MIKE...ie not exact but all matches. Hope understood my need. Please let me know if there is way to acheive this.

Please let know if you need some more inputs.

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
sandeep_reddy
Quartz | Level 8

Skillman,

Thanks for your reply.It is working fine now.I have one question regarding address standardization.

I have a fields named ADDRESS, which contains varchar data of 120 bytes(i.e. Sample is shown below). I need to standardize a portion of string, for example:

Address

#39/1, Electronic City, St. Green Glen, Bangalore

I need to convert St. to Street and rest all will be same (

#39/1, Electronic City, Street Green Glen, Bangalore

)

Kindly help me on this.

View solution in original post

10 REPLIES 10
Patrick
Opal | Level 21

I haven't used Data Management Studio yet but have an idea of the technologies it uses. What you could do is to create match codes and then join via these match codes - not sure how the nodes are called but I'm almost 100% sure that this can be done using DMS.

You could also standardize your 2 name columns (assigning the result to 2 new columns) and then join via these standardized columns.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Haven't used DMS either, but there are perl regular expressions - for SAS check out PRXMATCH PRXPARSE.  You can build up some complicated algorithms that way, or sql has the wildcards which may help (% _ etc.).

Depends on your data really, if there is lots of different variations then you would be best off re-coding as Patrick has responded.

skillman
SAS Employee

First you need to add match codes to your database lookup table (the one that the SQL Lookup node references).  Create name match codes and insert them into this table. Then when you are testing with the external data provider node, add a match code node for the name (using the same definition and sensitivity as you used in appending match codes to your lookup table.). When you are testing perform the SQL lookup on the match codes and not on the exact names and the following results will return Mike = Michael; MIKE = Michael; mike = MICHAEL.

-shawn

sandeep_reddy
Quartz | Level 8

HI shawn,

Thanks for your reply.I tried it what you had suggested. But it is not working as you said..it is fetching again the exact match only.

I tried with the following process.

first i created an output having matchcodes on names by using a data job.Then i used external data provider->match codes node->sql lookup.In sql lookup i had given the condition CUST_NAME_MATCHCODE=CUST_NAME_MATCHCODE.

I used the same definitions and same sensitivity.

Even after trying this it is giving exact matchcode for given input custopmer name.

Kindly help me on this..

Many thanks in advance

DaveR_SAS
SAS Employee

This might be more than you need, but the DM Studio user guide has a chapter on entity resolution that might help:

Working with Entity Resolutions

Reeza
Super User

SQL has a sounds like operator. You should be able to add custom sql to handle that. I'm not familiar with DMS to say how.

User Tips: Finding Similar Sounding Names using SOUNDEX - 4GuysFromRolla.com

=* in SAS

Find the SOUNDEX or SOUNDS LIKE for your DBMS as a starting point.

Patrick
Opal | Level 21

The (dataflux) match codes will give a much better result than soundex.

skillman
SAS Employee

Sandeep,

I created an example using the route I suggested and the one you mentioned. Everything worked as expected. See screenshots for a walkthrough:

Sample Process job with Match Code Creation and Test Data Jobs:

match_code_lookup_1.jpg

Match Code creation, insert into database:

match_code_lookup_2.jpg

Test with sample data (3 names):

match_code_lookup_3.jpg

Match code generation of the 3 sample names:

match_code_lookup_4.jpg

SQL Lookup into database using match codes created and results shown (Left 2 highlighted columns are my test data, right highlighted column are the matches returned from the database):

match_code_lookup_5.jpg

SQL Lookup dialog box:

match_code_lookup_6.jpg

Let me know if this helps,

-shawn

sandeep_reddy
Quartz | Level 8

Skillman,

Thanks for your reply.It is working fine now.I have one question regarding address standardization.

I have a fields named ADDRESS, which contains varchar data of 120 bytes(i.e. Sample is shown below). I need to standardize a portion of string, for example:

Address

#39/1, Electronic City, St. Green Glen, Bangalore

I need to convert St. to Street and rest all will be same (

#39/1, Electronic City, Street Green Glen, Bangalore

)

Kindly help me on this.

Patrick
Opal | Level 21

If you get your initial questions answered (quite a "service" skillman provided here for you) then mark the correct/most helpful answer as "correct answer". This closes the thread AND awards skillman the points he deserves.

Then start a new thread with your new question. If it's related to your previous thread then crosslink to this previous thread in your new thread. This way it's much easier for all of us to search the communities and find answers to our questions without having to look at 10mile long threads dealing with 20 different sub-discussions of which 19 are totally irrelevant for us.

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 2081 views
  • 4 likes
  • 7 in conversation