BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASPhile
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

8 REPLIES 8
Reeza
Super User

FINDW or INDEXW?

 

 

SASPhile
Quartz | Level 8

SAE_SUPP is not resulted out

art297
Opal | Level 21

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

 

 

SASPhile
Quartz | Level 8

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

Reeza
Super User
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.
SuryaKiran
Meteorite | Level 14
find(ColB,STRIP(ColA),'it')

Remove leading and trailing blanks.  

Thanks,
Suryakiran
art297
Opal | Level 21

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

 

 

ChrisNZ
Tourmaline | Level 20

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.

 

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
  • 8 replies
  • 11372 views
  • 1 like
  • 5 in conversation