topic Re: Prxmatch in SAS Programming

Prxmatch

SASPhile — Tue, 23 Jan 2018 20:11:53 GMT

I would like to use prxmatch on two columns from two tables in a proc Sql. But it doesn't seem to work.

Here is the logic I tried.

Proc sql;

Where prxmatch(a.text,b.text)>0

Quit;

Re: Prxmatch

ChrisNZ — Tue, 23 Jan 2018 20:23:04 GMT

Please look at the documentation. The first parameter must be a regular expression. I have to learn regular expressions before you can use that function.

Re: Prxmatch

art297 — Tue, 23 Jan 2018 20:28:20 GMT

Are you trying to do something like the following:

data have1;
  input text $;
  cards;
abc
bcd
bca
fgf
;
data have2;
  input text $;
  cards;
bcd
fgf
;

proc sql;
  select text
    into :from1
      separated by '|'
        from have2
  ;

  create table want as
    select text
      from have1
        where prxmatch('m/&from1/oi',text)>0
  ;
quit;

Art, CEO, AnalystFinder.com

Re: Prxmatch

SASPhile — Tue, 23 Jan 2018 20:28:46 GMT

I know, but something in that likes. A fuzzy match is required.

Re: Prxmatch

ChrisNZ — Tue, 23 Jan 2018 20:32:23 GMT

What are the match criteria? Have you looked at the spelling distance functions?

Re: Prxmatch

SASPhile — Tue, 23 Jan 2018 20:32:26 GMT

Yes Art, Something in that likes!

Re: Prxmatch

SASPhile — Tue, 23 Jan 2018 20:37:04 GMT

there are special characters to be compressed from both have1 and have2 as they are not consisternt for matching

Re: Prxmatch

art297 — Tue, 23 Jan 2018 20:41:52 GMT

Can you provide examples of both datasets and what you want to match from them?

Art, CEO, AnalystFinder.com

Re: Prxmatch

SASPhile — Tue, 23 Jan 2018 20:55:27 GMT

the data from two sources is like this,

Have1

Finance Exchequer Credit Bankrupt and Mortgage

Have2
Finance,exchequer,Credit Bankrupt and Mortgage

Trying to match with whole pattern or part of it.

Re: Prxmatch

ChrisNZ — Tue, 23 Jan 2018 20:56:39 GMT

Why not remove the unwanted characters with function compress, and then use spelling distance?

Re: Prxmatch

ChrisNZ — Tue, 23 Jan 2018 20:58:57 GMT

Your example matches with


upcase(compress(TEXT1,' ,')) =  upcase(compress(TEXT2,' ,'))

Re: Prxmatch

SASPhile — Tue, 23 Jan 2018 21:00:29 GMT

Not always do I get the same pattern. Thats why I'm relying on fuzzy match

Re: Prxmatch

art297 — Tue, 23 Jan 2018 21:06:16 GMT

Your example doesn't include any fuzzy or fuzzy-like instances. Can you provide an example that does. And, your example appears to have one record with just one variable (containing multiple words) in each file. Is that what you actually have?

Art, CEO, AnalystFinder.com

Re: Prxmatch

SASPhile — Tue, 23 Jan 2018 21:08:30 GMT

Yes Art!

Re: Prxmatch

SASPhile — Tue, 23 Jan 2018 21:08:56 GMT

even if partially matches that is fine too.

Re: Prxmatch

ChrisNZ — Tue, 23 Jan 2018 21:11:16 GMT

I'll ask again.

Have you looked at the spelling distance functions? like SPEDIS or COMPGED?

Re: Prxmatch

ChrisHemedinger — Tue, 23 Jan 2018 21:17:44 GMT

To build on @ChrisNZ's suggestion, check out this fuzzy matching tip from @Ron_Cody:

https://blogs.sas.com/content/sastraining/sas-authors-tip-spedis-and-fuzzy-matching/

Re: Prxmatch

SASPhile — Wed, 24 Jan 2018 14:46:26 GMT

Yes, they both are giving me the desired output.

I tried where soundex(a.text)=soundex(b.text), this did not yeild the result as well. Many of the words between the two strings match. Yet soundex fails.