DATA Step, Macro, Functions and more

string match (whole or partial)

Accepted Solution Solved
Reply
Super Contributor
Posts: 702
Accepted Solution

string match (whole or partial)

Need to compare two columns either complete or partial. This can be achived using regex,but do not want to hardcode the values as I need to compare 100's of records.

for instance:

 

ColA    ColB

AE       AE_NCI

AE       CM_AE

AE       SAE_SUPP

 

 

the colA has the string "AE" , if Colb has complete or partial that assign value as 1 . There are 100's of rows with different values that need to compared.

 

usual way is if prxmatch("/ae/oi",colb)>0 then value=1. but here ae is hardcoded. anyway to dymaically pass colA into prxmatch function?


Accepted Solutions
Solution
‎02-13-2018 04:24 PM
PROC Star
Posts: 8,115

Re: string match (whole or partial)

I agree with @SuryaKiran. If your colA includes leading and trailing blanks, then use the strip function. However, then remove the t option as it will only cause the process to slow down.  i.e.:

data want;
  set have;
  if find(ColB,strip(ColA),'i') then value=1;
  else value=0;
run;

Art, CEO, AnalystFinder.com

 

p.s., @SASPhile: This is ArtT not ArtC

 

 

View solution in original post


All Replies
Super User
Posts: 22,874

Re: string match (whole or partial)

FINDW or INDEXW?

 

 

Super Contributor
Posts: 702

Re: string match (whole or partial)

SAE_SUPP is not resulted out

PROC Star
Posts: 8,115

Re: string match (whole or partial)

How about:

data have;
  input (ColA    ColB) ($);
  cards;
AE       AE_NCI
AE       CM_AE
AE       SAE_SUPP
AE       AB_NCI
AB       AB_NCI
AB       CM_AE
AB       SAB_SUPP
;

data want;
  set have;
  if find(ColB,ColA,'it') then value=1;
  else value=0;
run;

Art, CEO, AnalystFinder.com

 

 

Super Contributor
Posts: 702

Re: string match (whole or partial)

Artc, I'm getting the value  0 for all records

Super User
Posts: 22,874

Re: string match (whole or partial)

I ran @art297 code and it worked fine with the demo data. Does it work with the demo data he shows? Does it not work with your actual data instead. Also, Art is correct, if you want partial searchs,use FIND, if searching for a specific character it's FINDC, and for a word it's FINDW.
Super Contributor
Posts: 478

Re: string match (whole or partial)

find(ColB,STRIP(ColA),'it')

Remove leading and trailing blanks.  

Thanks,
Suryakiran
Solution
‎02-13-2018 04:24 PM
PROC Star
Posts: 8,115

Re: string match (whole or partial)

I agree with @SuryaKiran. If your colA includes leading and trailing blanks, then use the strip function. However, then remove the t option as it will only cause the process to slow down.  i.e.:

data want;
  set have;
  if find(ColB,strip(ColA),'i') then value=1;
  else value=0;
run;

Art, CEO, AnalystFinder.com

 

p.s., @SASPhile: This is ArtT not ArtC

 

 

PROC Star
Posts: 2,231

Re: string match (whole or partial)

Like this?

data HAVE;
  input COLA $ COLB $;
  A= prxmatch(catt('/(',COLA,')/i'),COLB);
  putlog A=;
cards;
AE AE_NCI
AE CM_AE
AE SAE_SUPP
AX SAE_SUPP
run;

A=1
A=4
A=2
A=0

RegEx are more expensive than functions like FIND though.

 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 146 views
  • 0 likes
  • 5 in conversation