topic Re: How to replace characters in a string that follow certain characters? in SAS Programming

How to replace characters in a string that follow certain characters?

ginak — Tue, 12 May 2020 21:03:41 GMT

Hi there,

I have a dataset with long text fields. I need to replace every instance of a dollar amount, say, "439.33" with "439.33USD"

Here's a sample data set:

data one;
infile cards  ;
length name $200;
input name;
cards;
Ccy="USD">738.56</NS!figlwijfoaiu345Ccy="USD">38.44</ScT@
run;

So what I have is:

What I want is USD to be present after every dollar amount as is here:

I found two different community forums, and know that I will need a combination of

index
1. To find the decimal point that follows any "USD" in the text
substr
tranwrd functions
1. To replace that dollar amount with [dollaramount]USD

There could be multiple instances of Ccy="USD">[dollar amount] in the string. I just want to tack on the USD after the dollar amount, but not every dollar amount is XYZ.AB, some are YZ.AB, some are Z.AB etc. There will always be a decimal point with two decimal places though that follow the Ccy="USD"

Thanks!

Re: How to replace characters in a string that follow certain characters?

ChrisNZ — Tue, 12 May 2020 21:47:35 GMT

Is it always USD? Or are there other currencies?

Re: How to replace characters in a string that follow certain characters?

ChrisNZ — Tue, 12 May 2020 23:32:07 GMT

This replaces:

at least a digit followed by a dot followed by 2 digits

with:

the group found followed by USD.

data TWO;
 set ONE; 
 NAME2=prxchange('s/(\d+\.\d\d)/\1USD/',-1,NAME);
run;

{edited to avoid losing one character}

Re: How to replace characters in a string that follow certain characters?

ginak — Tue, 12 May 2020 22:21:06 GMT

Hi there, yes it'll always say "USD" (and include the double quotes in the text too). Thanks!

Re: How to replace characters in a string that follow certain characters?

ginak — Tue, 12 May 2020 22:41:57 GMT

I think this works, thank you! It works with my fake sample data but I'll try it with my actual data tomorrow and let you know if I have any questions 🙂 I never understood the prxchange command and where it comes from. Would love to learn more since it seems so useful

Re: How to replace characters in a string that follow certain characters?

ChrisNZ — Tue, 12 May 2020 23:34:03 GMT

The syntax is that of Perl regular expressions.

Tons of tutorials on the web, that's how I learnt.

Re: How to replace characters in a string that follow certain characters?

s_lassen — Wed, 13 May 2020 08:38:45 GMT

The solution presented by @ChrisNZ goes part of the way, but I would add a bit to make sure that only the right numbers get selected:

name=prxchange('s/("USD">\d+\.\d\d)(?=<)/\1USD/',-1,name);

This looks for the string "USD"> followed by at least one digit, a period and two digits (that's the stuff in the first paranthesis). The whole thing should be followed by "<", but that string is not included in the match (the second paranthesis, "?=" denotes a look-ahead buffer), the match is replaced with the same plus the text "USD".