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)
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}/'.
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
Sure, I've attached a short representation of the two tables, and the desired output from the PRXMATCH statement.
Thanks, Ian
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}/'.
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.
| means or
This should work fine, I was being lazy and trying to get countries linked in through a table join
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.