SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

Accepted Solution Solved
Reply
Contributor
Posts: 66
Accepted Solution

In Data management studio, SQL lookup fetches exact match but how to get pattern match data.


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.


Accepted Solutions
Solution
‎03-05-2014 05:56 AM
Contributor
Posts: 61

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

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


All Replies
Respected Advisor
Posts: 4,173

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

Posted in reply to maheshtalla

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.

Super User
Super User
Posts: 7,993

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

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.

SAS Employee
Posts: 85

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

Posted in reply to maheshtalla

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

Contributor
Posts: 61

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

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

SAS Employee
Posts: 75

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

Posted in reply to sandeep_reddy

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

Super User
Posts: 19,869

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

Posted in reply to maheshtalla

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.

Respected Advisor
Posts: 4,173

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

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

SAS Employee
Posts: 85

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

Posted in reply to maheshtalla

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

Solution
‎03-05-2014 05:56 AM
Contributor
Posts: 61

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

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.

Respected Advisor
Posts: 4,173

Re: In Data management studio, SQL lookup fetches exact match but how to get pattern match data.

Posted in reply to sandeep_reddy

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

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