topic Re: Multiple regex query in SAS Enterprise Guide

Multiple regex query

ICL1986 — Fri, 27 Oct 2017 09:00:51 GMT

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)

Re: Multiple regex query

kiranv_ — Fri, 27 Oct 2017 13:17:20 GMT

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

Re: Multiple regex query

ICL1986 — Fri, 27 Oct 2017 13:30:25 GMT

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

Thanks, Ian

Re: Multiple regex query

kiranv_ — Fri, 27 Oct 2017 14:14:15 GMT

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}/'.

Re: Multiple regex query

ICL1986 — Fri, 27 Oct 2017 14:25:01 GMT

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.

Re: Multiple regex query

kiranv_ — Fri, 27 Oct 2017 14:26:40 GMT

| means or

Re: Multiple regex query

ICL1986 — Fri, 27 Oct 2017 14:30:37 GMT

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