BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Tom
Super User Tom
Super User

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

Tom_1-1722261078083.png

 

 

 

mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 3902 views
  • 3 likes
  • 6 in conversation