Remove the uppercase in string

[SASdevAnneMarie]

Hello Experts,

The string in my data is like :

Message 

During bankAccount 'BSD0139' .

Error 'MATF081' csv.

Solvecy bank 'MATF026' line

Programme '22337' run

I would like to remove all the codes like BSD0139.

Do you know please the function for it ? When I'm doing translate(Message,'','0123456789'), I remove only the numeric values.

Thank you for your help !

[PaigeMiller]

Does the string ALWAYS begin with

During bankAccount 

or can there be other beginning strings?

Does the uppercase you want to remove ALWAYS begin in column 20? Or can it be other places in the string?

Please explain the logic by which a programmer could find this string in the larger text string.

[SASdevAnneMarie]

Hello,

Sorry, I udated my question.
I would like to remove the code 'FFYY77', 'KKLM', the code is always in uppercase. The string is variable, but I have a one code in the string.

[PaigeMiller]

So when I ask for information

Please explain the logic by which a programmer could find this string in the larger text string.

I am expecting you to respond to this request, but I don't see one.

In addition, you have now changed the problem to where the string of interest is now in quotes (whereas before it was not in quotes). Is that how the data really appears, with quotes around the string you want to remove?? Or not?

[Tom]

Not sure what UPPERCASE has to do with your quesition.

If looks like you want to remove the word that follows the word "bankAccount".

Is that the actual problem you are trying to solve?

NOTE: The TRANSLATE() function converts individual characters.  Your code was just converting the digits into spaces.

[SASdevAnneMarie]

Hello,
Sorry, I udated my question.
I would like to remove the code 'FFYY77', 'KKLM', the code is always in uppercase. The string is variable, but I have a one code in the string.

[ballardw]

---

@SASdevAnneMarie wrote:
Hello,
Sorry, I udated my question.
I would like to remove the code 'FFYY77', 'KKLM', the code is always in uppercase. The string is variable, but I have a one code in the string.

---

Show example data that matches your requirement and the desired result. At the time I read this your "data" has no values containing FFYY77 or KKLM

If it is only those values than perhaps TRANWRD. If it is based on position in a longer string then provide actual examples.

What I see in your first post is this:

The string in my data is like :

Message 

During bankAccount 'BSD0139' .

Error 'MATF081' csv.

Solvecy bank 'MATF026' line

Programme '22337' run

I would like to remove all the codes like BSD0139.

Do you know please the function for it ? When I'm doing translate(Message,'','0123456789'), I remove only the numeric values.

Thank you for your help !

[SASdevAnneMarie]

Thank you Ballardw,

I added the data.

[AMSAS · Accepted solution]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

The best way to provide data is via the following:

Data2DataStep Maco instructions will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

Having said that, and then making some assumptions about your data, you probably need to be looking at Regular Expressions

Here's an example that might do what you want:

data have ;
	infile cards ;
	input string $40. ;
cards ;
During bankAccount BSD0139 update.
During bankAccount MATF081 update.
During bankAccount MATF026 update
during bankAccount 22337 update
;
run ;

data want ;
	set have ;
	newString=prxchange('s/[A-Z]{3,4}[0-9]{3,4} //', -1, string);
	put string= @50 newString= ;
run ;

[SASdevAnneMarie]

Thank you, Amsas,

Could you please explain what means {3,4} in your code ?

[Patrick]

Using Regular Expressions is one way to go about this. 

To define the appropriate Regular Expression one needs to fully understand the text pattern of your source data.

Based on the sample data you've posted in your Excel attachment below RegEx could work.

data have;
  infile datalines truncover;
  input message $80.;
datalines;
Error during bankAccount CCBP294 update
Error during bankAccount CCBP297 update
Error during bankAccount UBS0138 update
Error during creation of bankAccount BGPL590 for csv line 12311
Error during bankAccount BPLL009 update
Error during bankAccount BPMC001 update
Error during creation of bankAccount CICI002 for csv line 13289
Error during bankAccount BCA1240 update
Error during creation of bankAccount MILL007 for csv line 13427
Error during bankAccount KBL0386 update
Error during bankAccount UBS0417 update
Error during bankAccount WARG100 update
Error during bankAccount BGPJ435 update
Error during bankAccount BSD1587 update
Error during creation of bankAccount COPA137 for csv line 14607
Error during deactivation of company 290
Error during deactivation of company 291
Error during deactivation of company 292
Error during deactivation of company 293
Error during deactivation of company 294
;

data want;
  set have;
  if 0 then message_2=message;
  message_2=prxchange('s/^(.*?bankaccount) \b\w*\b(.*)$/$1 <removed>$2/oi',1,strip(message));
run;

proc print data=want;
run;