BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Hoibai
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

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
 

 

 

 

 

View solution in original post

16 REPLIES 16
PaigeMiller
Diamond | Level 26

@Hoibai 

Please tell us what this regexp_extract function does.

--
Paige Miller
Hoibai
Obsidian | Level 7

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

 

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

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

 

Quentin
Super User

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

ChrisNZ
Tourmaline | Level 20

Like this?

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

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);

 

 

Hoibai
Obsidian | Level 7

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.

ChrisNZ
Tourmaline | Level 20
That's why I use a variable name. Whatever is in STR1 will be read into STR2.
Swap with the names of your variables.
Hoibai
Obsidian | Level 7

my string is 300 - 400 Bytes long.

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

ChrisNZ
Tourmaline | Level 20
The parsing should still work. Have you tried?
Hoibai
Obsidian | Level 7

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

 

Quentin
Super User

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.

ChrisNZ
Tourmaline | Level 20

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
 

 

 

 

 

Hoibai
Obsidian | Level 7

This is the best solution. Thank you very much.

Hoibai
Obsidian | Level 7

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 16 replies
  • 3911 views
  • 3 likes
  • 6 in conversation