topic Re: Is there a comparable function in sas like regexp_extract? in SAS Programming

Is there a comparable function in sas like regexp_extract?

Hoibai — Mon, 29 Jul 2024 11:11:08 GMT

I have this string: "Cdtr":{"Nm":"DEUTSCHE BANK"}

How can I implement this in SAS like the function

regexp_extract(fulltxinf_intx, '"Cdtr":."Nm":"([^"]+)', 1) as String_Ort

Thanks

Re: Is there a comparable function in sas like regexp_extract?

PaigeMiller — Mon, 29 Jul 2024 11:14:12 GMT

@Hoibai

Please tell us what this regexp_extract function does.

Re: Is there a comparable function in sas like regexp_extract?

Hoibai — Mon, 29 Jul 2024 11:29:47 GMT

The function returns me the result DEUTSCHE BANK, that the parameters Cdtr":{"Nm": are in front of it.

My string: "Cdtr":{"Nm":"DEUTSCHE BANK"}

regexp_extract(string, '"Cdtr":."Nm":"([^"]+)', 1) as String_Ort

Re: Is there a comparable function in sas like regexp_extract?

Quentin — Mon, 29 Jul 2024 12:24:28 GMT

Can you give a few more examples of strings you would like to parse?

Re: Is there a comparable function in sas like regexp_extract?

ChrisNZ — Mon, 29 Jul 2024 12:29:45 GMT

Like this?

data T;
  STR1 = '"Cdtr":{"Nm":"DEUTSCHE BANK"}';
  STR2 = prxchange('s/"Cdtr":."Nm":"([^"]+).*/\1/', 1, STR1);
  put STR2=;
run;

STR2=DEUTSCHE BANK

Re: Is there a comparable function in sas like regexp_extract?

ChrisNZ — Mon, 29 Jul 2024 12:32:33 GMT

Not too sure why you have

  STR2 = prxchange('s/"Cdtr":."Nm":"([^"]+).*/\1/', 1, STR1);

I'd replace the . with a {

  STR2 = prxchange('s/"Cdtr":{"Nm":"([^"]+).*/\1/', 1, STR1);

Re: Is there a comparable function in sas like regexp_extract?

Hoibai — Mon, 29 Jul 2024 12:55:03 GMT

The problem is, the first record is with {"Cdtr":{"Nm":"DEUTSCHE BANK"} .

The second record is with '{"Cdtr":{"Nm":"VERGOELST GMBH"}'

according to the constant variables {"Cdtr":{"Nm":, each record in the file has a different value.

Re: Is there a comparable function in sas like regexp_extract?

ChrisNZ — Mon, 29 Jul 2024 13:12:48 GMT

That's why I use a variable name. Whatever is in STR1 will be read into STR2.
Swap with the names of your variables.

Re: Is there a comparable function in sas like regexp_extract?

Hoibai — Mon, 29 Jul 2024 13:18:45 GMT

my string is 300 - 400 Bytes long.

{"Cdtr":{"Nm":"DEUTSCHE BANK"},"CdtrAcct":{"Id":{"IBAN":"DE0111111111111111111100"}},

Re: Is there a comparable function in sas like regexp_extract?

ChrisNZ — Mon, 29 Jul 2024 13:21:00 GMT

The parsing should still work. Have you tried?

Re: Is there a comparable function in sas like regexp_extract?

Hoibai — Mon, 29 Jul 2024 13:36:07 GMT

my string begins

{"ar:pacs.003.002.04":{"Cdtr":{"Nm":"DEUTSCHE BANK"},"CdtrAcct":{"Id":{"IBAN":"DE0111111111111111111100"}}

that's why I get this result: {"ar:pacs.003.002.04":{DEUTSCHE BANK

Re: Is there a comparable function in sas like regexp_extract?

Tom — Mon, 29 Jul 2024 13:51:33 GMT

@Hoibai wrote:

The problem is, the first record is with {"Cdtr":{"Nm":"DEUTSCHE BANK"} .

The second record is with '{"Cdtr":{"Nm":"VERGOELST GMBH"}'

according to the constant variables {"Cdtr":{"Nm":, each record in the file has a different value.

I don't understand what you mean be this. Of course each observation has a different value. But the PREFIX you are searching for looks exactly the same in both of those example values.

{"Cdtr":{"Nm":"

Personally I avoid having to use regex if I can get the result I want with simpler functions.

data have;
  infile cards truncover;
  input str $200.;
cards4;
{"Cdtr":{"Nm":"DEUTSCHE BANK"}
{"Cdtr":{"Nm":"VERGOELST GMBH"}
{"ar:pacs.003.002.04":{"Cdtr":{"Nm":"DEUTSCHE BANK"},"CdtrAcct":{"Id":{"IBAN":"DE0111111111111111111100"}}
;;;;

data want;
  length cdtr_nm $60 ;
  set have;
  sp=index(str,'{"Cdtr":{"Nm":');
  if sp then cdtr_nm = dequote(substr(str,sp+length('{"Cdtr":{"Nm":')));
run;

Re: Is there a comparable function in sas like regexp_extract?

Quentin — Mon, 29 Jul 2024 13:53:04 GMT

Can you please give a few examples (just 5-10) of the full string you are trying to parse? It will help people help you.

Re: Is there a comparable function in sas like regexp_extract?

ChrisNZ — Tue, 30 Jul 2024 00:03:47 GMT

that's why I get this result: {"ar:pacs.003.002.04":{DEUTSCHE BANK

data HAVE;
  input STR1 &:$300.;
cards;  
{"ar:pacs.003.002.04":{"Cdtr":{"Nm":"DEUTSCHE BANK"},"CdtrAcct":{"Id":{"IBAN":"DE0111111111111111111100"}}
{"Cdtr":{"Nm":"DEUTSCHE BANKX"},"CdtrAcct":{"Id":{"IBAN":"DE0111111111111111111100"}}
{"ar:pacs.003.002.04":{"Cxxx":{"Nm":"DEUTSCHE BANKY"},"CdtrAcct":{"Id":{"IBAN":"DE0111111111111111111100"}}
run;

proc sql; 
  select ifc( prxchange('s/.*"Cdtr":{"Nm":"([^"]+).*/\1/', 1, STR1) = STR1, ' ', prxchange('s/.*"Cdtr":{"Nm":"([^"]+).*/\1/', 1, STR1) ) as STRING_ORT
  from HAVE;
quit;

I cater for cases when there is not matching pattern with the ifc() test.

STRING_ORT
DEUTSCHE BANK
DEUTSCHE BANKX

Re: Is there a comparable function in sas like regexp_extract?

mkeintz — Tue, 30 Jul 2024 03:42:18 GMT

Do they all have the form

"Cdtr":{"Nm":"XXX XXXX XXXXXX"}

where "XXX XXXX XXXXXX" is the desired result, and can have variable length?

If so, you can always skip the first 14 characters and drop the last 2 characters, which can be done with the SUBSTR and LENGTH functions:

data sample;
  string='"Cdtr":{"Nm":"DEUTSCHE BANK"}';
  new_string=substr(string,15,length(string)-16);

  put (_all_) (= /);
run;

Or if you want something more general, perhaps you can characterize the desired substring as the 5th word, where each double quote is treated as a word separator. Then, as long as the desired expression is double-quoted, and it is preceded by two expressions that are also double quoted (i.e. preceded by 4 double quotes), you can do this:

data sample;
  string='"Cdtr":{"Nm":"DEUTSCHE BANK"}';
  new_string=scan(string,5,'"');
  put (_all_) (= /);
run;

Re: Is there a comparable function in sas like regexp_extract?

Hoibai — Tue, 30 Jul 2024 08:52:17 GMT

This is the best solution. Thank you very much.

Re: Is there a comparable function in sas like regexp_extract?

Hoibai — Mon, 05 Aug 2024 08:57:57 GMT

This solution for SAS can 100% replace the regexp_extract function.