Hello Experts,
My data is:
I would like to replace "0" by space before and after the values:
Mt="0000000302972E" -> Mt_final= 302972E
Mt="0000000141852A0000" ->Mt-final= 141852A
My code is :
data table_2;
set table_1;
if prxmatch("m/0/oi",mt)> 0 then
mt_final=tranwrd(mt,"0","");
run;
But some times with the "0" inside the value like Mt="0000000302972E" it doesn't work because I get Mt_final= 3 2972E, not Mt_final= 302972E.
I'm wondering if there is some option in prxmatch function.
Thank you for helping me !
Hello @SASdevAnneMarie,
Try this:
data have;
input mt $20.;
cards;
0000000302972E
0000000141852A0000
141852A0000
0014000001852A0
141852A
0014001800502A0
000000000000000
;
data want;
set have;
length mt_final $20;
mt_final=prxchange('s/^0*(.*[^0])0*$/\1/', 1, strip(mt));
run;
Explanation: After removing leading and trailing blanks from mt (STRIP function) search for the three-part pattern "leading zeros (if any), a sequence of arbitrary characters ending with a non-zero character, trailing zeros (if any)" and replace it by that "middle" part.
Hello @SASdevAnneMarie,
Try this:
data have;
input mt $20.;
cards;
0000000302972E
0000000141852A0000
141852A0000
0014000001852A0
141852A
0014001800502A0
000000000000000
;
data want;
set have;
length mt_final $20;
mt_final=prxchange('s/^0*(.*[^0])0*$/\1/', 1, strip(mt));
run;
Explanation: After removing leading and trailing blanks from mt (STRIP function) search for the three-part pattern "leading zeros (if any), a sequence of arbitrary characters ending with a non-zero character, trailing zeros (if any)" and replace it by that "middle" part.
@SASdevAnneMarie wrote:
Hello, thank you very much, that works! I never use the prxchange before. Could you explain me please : leading zeros (if any): is it .*[^0] ? The sequence of arbitrary characters ending with a non-zero character: is it 0*$ ? trailing zeros (if any) ^0* ? Replace it by that "middle" part - is it "1" ?
You're welcome. See the Tables of Perl Regular Expression (PRX) Metacharacters to find out:
The 1 in the second argument of the PRXCHANGE function is the number of times the replacement is to be executed (per call of the function). Here, a single replacement is sufficient.
Masters of the PRX functions like @Ksharp are likely to improve on any given regular expression. His elegant "^0+|0+$" matches one or more leading zeros (^0+) or (|) one or more trailing zeros (0+$) and he just removes these matched parts of the string in the result (replacement = empty string). Note that now a single replacement is not sufficient because if both leading and trailing zeros are found, the first replacement will only remove the leading blanks. Therefore the second argument of PRXCHANGE must be a number >1 or simply equal to -1 (to continue the replacements until the end of the source string is reached). Also note that if strip(mt) is just a sequence of zeros (see last observation in dataset HAVE) Ksharp's regular expression creates a blank string, whereas mine leaves the zeros unchanged because no non-zero character ([^0]) is found.
data have; input mt $20.; cards; 0000000302972E 0000000141852A0000 141852A0000 0014000001852A0 141852A 0014001800502A0 000000000000000 ; data want; set have; length mt_final $20; mt_final=prxchange('s/^0+|0+$//', -1, strip(mt)); run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.