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

Hi, I'm working with some banking data containing IBANs, and would like to use a list of regular expressions (using SUBSTR and PRXMATCH) to extract them from text strings. I've tried creating a separate table containing the regex, for example ‘/AL\d{24}/’,
 '/AD\d{20}/’, ‘/AT\d{16}/’, '/LV\d{2}\D{4}\d{13}/', and then bringing that in as a column to query against the data, but am getting no results. Does anyone know how I might be able to do this? Current PRXMATCH expression is:

 

PRXMATCH(t2.Regex,t1.TextcolumnwithIBAN)

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

still it is not very clear to me what code you have tried. But if you are using multiple values you need to use a pipe not a comma.

comma

‘/AL\d{24}/’,'/AD\d{20}/’, ‘/AT\d{16}/’, '/LV\d{2}\D{4}\d{13}/'

 

 pipe

‘/AL\d{24}/’|'/AD\d{20}/’| ‘/AT\d{16}/’| '/LV\d{2}\D{4}\d{13}/'.

 

 

View solution in original post

6 REPLIES 6
kiranv_
Rhodochrosite | Level 12

it will be easier to help, if you can provide an example/sample of what is not working and what your output should look like

ICL1986
Fluorite | Level 6

Sure, I've attached a short representation of the two tables, and the desired output from the PRXMATCH statement.

 

Thanks, Ian

kiranv_
Rhodochrosite | Level 12

still it is not very clear to me what code you have tried. But if you are using multiple values you need to use a pipe not a comma.

comma

‘/AL\d{24}/’,'/AD\d{20}/’, ‘/AT\d{16}/’, '/LV\d{2}\D{4}\d{13}/'

 

 pipe

‘/AL\d{24}/’|'/AD\d{20}/’| ‘/AT\d{16}/’| '/LV\d{2}\D{4}\d{13}/'.

 

 

ICL1986
Fluorite | Level 6

With the two tables I attached, I created a query within Table 1, added Table 2 to the query, and then added a column with the advanced expression PRXMATCH(Table 2.IBAN Regex, Table 1.Text Column). There are just under 70 IBAN types but I suppose using the | divider would work, and then I could extract countries from there.

kiranv_
Rhodochrosite | Level 12

| means or

ICL1986
Fluorite | Level 6

This should work fine, I was being lazy and trying to get countries linked in through a table join

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 1313 views
  • 0 likes
  • 2 in conversation