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

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

maheshtalla — Mon, 03 Mar 2014 09:30:06 GMT

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.

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

Patrick — Mon, 03 Mar 2014 09:46:01 GMT

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.

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

RW9 — Mon, 03 Mar 2014 15:29:13 GMT

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.

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

skillman — Mon, 03 Mar 2014 16:18:24 GMT

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

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

Reeza — Mon, 03 Mar 2014 16:23:17 GMT

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.

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

Patrick — Mon, 03 Mar 2014 21:49:00 GMT

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

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

sandeep_reddy — Tue, 04 Mar 2014 04:36:24 GMT

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

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

DaveR_SAS — Tue, 04 Mar 2014 16:03:47 GMT

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

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

skillman — Tue, 04 Mar 2014 16:35:57 GMT

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 creation, insert into database:

Test with sample data (3 names):

Match code generation of the 3 sample names:

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

SQL Lookup dialog box:

Let me know if this helps,

-shawn

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

sandeep_reddy — Wed, 05 Mar 2014 10:56:08 GMT

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.

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

Patrick — Wed, 05 Mar 2014 11:12:34 GMT

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.