BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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;
17 REPLIES 17
ChrisNZ
Tourmaline | Level 20

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.

SASPhile
Quartz | Level 8

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

ChrisNZ
Tourmaline | Level 20

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

art297
Opal | Level 21

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

 

SASPhile
Quartz | Level 8

Yes Art, Something in that likes!

SASPhile
Quartz | Level 8

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

art297
Opal | Level 21

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

 

Art, CEO, AnalystFinder.com

 

SASPhile
Quartz | Level 8

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.

ChrisNZ
Tourmaline | Level 20

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

ChrisNZ
Tourmaline | Level 20

Your example matches with 


upcase(compress(TEXT1,' ,')) =  upcase(compress(TEXT2,' ,'))
SASPhile
Quartz | Level 8

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

art297
Opal | Level 21

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

 

SASPhile
Quartz | Level 8

Yes Art!

SASPhile
Quartz | Level 8
even if partially matches that is fine too.

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 17 replies
  • 1676 views
  • 0 likes
  • 4 in conversation