topic Re: prxchange with macro in SAS Programming

prxchange with macro

GingerJJ — Fri, 20 Aug 2021 18:21:08 GMT

ARRAY NAMES[*] $ 40 LN FN ;
DO I = 1 to 2;
NAMES [I]= ORIGINAL [I];

END;
DO I = 1 to 2;
/* turn anything that is NOT a letter into space*/
NAMES[I] = prxchange("s/[^A-Z]/ /",-1,NAMES[I]);
NAMES[I] = prxchange("s/"&BAD_PART"/ /",-1, left(trim(NAMES[I])));
NAMES[I] = STRIP(NAMES[I]);/*removing all front and back space*/
NAMES[I] = COMPBL(NAMES[I]); /*Change multiple blanks into one*/;

END;
DROP I;
RUN;

It won't run. Can someone help me?

Thank you!

@maguiremq

Re: prxchange with macro

maguiremq — Fri, 20 Aug 2021 18:32:59 GMT

Hey @GingerJJ, good to see you again.

What does your test1 dataset look like? I would need to see what's going on there before I or anyone else can help.

If you can, please provide it in a DATALINES statement so that we can reproduce it on our computers. We usually don't like downloading things due to potential risks.

Edit: What happens when you remove the double quotes around &bad_part.?

Thanks,

Michael

Re: prxchange with macro

GingerJJ — Fri, 20 Aug 2021 19:13:39 GMT

Thanks for getting back to me so quick.

It got rid of all the error messages after I remove the "" around &BAD_PART. But, it didn't get rid of the bad parts at all.

Dataline;

C_LN C_FN

DUPLICATE-DO NOT USE John234
DO NOT USE (DUPLICATE) DUPLICATE RECORD
DO NOT USE DUPLICATE FILE [DONOT USE]
DUPLICATE FILE DUPLICATE FILE 'DONT USE'
(DUPLICATE) "DUPLICATE"

Re: prxchange with macro

maguiremq — Fri, 20 Aug 2021 19:31:16 GMT

Okay, and what do you want it to look like?

Also, here's how you'd post it to us to make things easier:

data test1;
infile datalines delimiter = "," truncover;
input c_ln :$50. c_fn :$50.;
datalines;
DUPLICATE-DO NOT USE, John234
DO NOT USE (DUPLICATE), DUPLICATE RECORD
DO NOT USE DUPLICATE FILE, [DONOT USE]
DUPLICATE FILE DUPLICATE FILE, 'DONT USE'
(DUPLICATE),"DUPLICATE"
;

Re: prxchange with macro

GingerJJ — Fri, 20 Aug 2021 19:32:42 GMT

I want turn everything in the BAD_PART into a space.

But first I want to turn all the special characters and numbers into space.

Re: prxchange with macro

maguiremq — Fri, 20 Aug 2021 19:48:27 GMT

Here's part 1:

%let bad_part2 = %sysfunc(tranwrd(&bad_part., %str(|), %str( )));

 %put &bad_part2.;
DUPLICATE DO NOT USE DONOT USE FILE RECORD DO NOT ISSUE DONT USE

data want;
	set test1;
		c_ln_1 = prxchange("s/[^A-Za-z]+/ /", -1, trim(c_ln));
		c_fn_1 = prxchange("s/[^A-Za-z]+/ /", -1, trim(c_fn));
run;

proc print data = want;
run;

Obs	c_ln	c_fn	c_ln_1	c_fn_1
1	DUPLICATE-DO NOT USE	John234	DUPLICATE DO NOT USE	John
2	DO NOT USE (DUPLICATE)	DUPLICATE RECORD	DO NOT USE DUPLICATE	DUPLICATE RECORD
3	DO NOT USE DUPLICATE FILE	[DONOT USE]	DO NOT USE DUPLICATE FILE	DONOT USE
4	DUPLICATE FILE DUPLICATE FILE	'DONT USE'	DUPLICATE FILE DUPLICATE FILE	DONT USE
5	(DUPLICATE)	"DUPLICATE"	DUPLICATE	DUPLICATE

Re: prxchange with macro

GingerJJ — Fri, 20 Aug 2021 19:51:26 GMT

Got it! Will do in the future!

Re: prxchange with macro

GingerJJ — Fri, 20 Aug 2021 19:55:04 GMT

Sorry I didn't make myself clear.

I want to get rid of all the phrases in BAD_PART and turn that into a single space.

eg. turn "DUPLICATE John" into " John"

Re: prxchange with macro

maguiremq — Fri, 20 Aug 2021 20:02:22 GMT

So is John the only thing left?

data want;
	set test1;
		c_ln_1 = prxchange("s/[^A-Za-z]+/ /", -1, trim(c_ln));
		c_fn_1 = prxchange("s/[^A-Za-z]+/ /", -1, trim(c_fn));
		c_ln_2 = prxchange("s/(&bad_part.)/ /", -1, trim(c_ln_1));
		c_fn_2 = prxchange("s/(&bad_part.)/ /", -1, trim(c_fn_1));
run;

Re: prxchange with macro

GingerJJ — Fri, 20 Aug 2021 20:06:13 GMT

It fixed it!

Thank you so much!

c_ln_1 = prxchange("s/[^A-Za-z]+/ /", -1, trim(c_ln));

What is the "+" for in the above statement?

Re: prxchange with macro

maguiremq — Fri, 20 Aug 2021 20:14:50 GMT

The plus is considered a 'greedy match'. It takes however many combinations of whatever you're requesting. If it finds one character, it will keep that one character. If there are two, it will keep those two. If there are 90, it will keep 90.

Re: prxchange with macro

Tom — Fri, 20 Aug 2021 20:27:21 GMT

@maguiremq wrote:

The plus is considered a 'greedy match'. It takes however many combinations of whatever you're requesting. If it finds one character, it will keep that one character. If there are two, it will keep those two. If there are 90, it will keep 90.

That is more complicated explanation than I would have expected.

+ means match 1 or more.

* means match 0 or more.

Since you mentioned greedy match what would be the way to do a non-greedy match? I don't think there is one for this example but perhaps you could provide a better example that demonstrates a non-greedy match and explain the RegEx symbols needed for that.

Re: prxchange with macro

Tom — Fri, 20 Aug 2021 20:30:58 GMT

I don't understand how the macro processor comes into this.

Your RegEx code is running in a data step.

Re: prxchange with macro

GingerJJ — Fri, 20 Aug 2021 20:33:06 GMT

NAMES[I] = prxchange("s/[^A-Z]/ /",-1,trim(NAMES[I]));

The above code also serves the purpose without the "+". So when is it absolutely necessary to add the "+"?

Re: prxchange with macro

Tom — Fri, 20 Aug 2021 20:40:05 GMT

Try it and see if there is a difference.

Look carefully at how many spaces are used to replace the non uppercase letters.

 69         data test;
 70          x='AbcdFGHxyz';
 71          x2 = prxchange("s/[^A-Z]/ /",-1,x);
 72          x3 = prxchange("s/[^A-Z]+/ /",-1,x);
 73          put (_all_) (=:$quote./);
 74         run;
 
 x="AbcdFGHxyz"
 x2="A   FGH"
 x3="A FGH"

Re: prxchange with macro

maguiremq — Sun, 22 Aug 2021 12:29:18 GMT

No, @Tom, you're completely right. I don't know what I was thinking that day. Must have been the end-of-workweek blues.

Re: prxchange with macro

ChrisNZ — Mon, 23 Aug 2021 00:30:20 GMT

This is all you need:

NAMES[I] = prxchange("s/&BAD_PART|[^A-Z]+/ /", -1, trim(NAMES[I]));
NAMES[I] = left(compbl(NAMES[I]));

The + and the trim() serve no purpose but to improve performance.

Re: prxchange with macro

GingerJJ — Tue, 24 Aug 2021 15:05:33 GMT

Thank you all that helped!

I'm really touched by so much support here!

Re: prxchange with macro

GingerJJ — Fri, 27 Aug 2021 17:44:53 GMT

data test1;
infile datalines delimiter = "," truncover;
input c_ln :$50. c_fn :$50.;
datalines;
DUPLICATE-DO NOT USE, JOHN234
DO NOT USE (DUPLICATE), DUPLICATE RECORD
DO NOT USE DUPLICATE FILE, [DONOT USE]
DUPLICATE FILE DUPLICATE FILE, 'DONT USE'
(DUPLICATE),"DUPLICATE"
DE LA MARIA, MARIA JR.
MARIA DEL ROSA, MARIA DELROSA
MARIA DE LA ROSA, MARIA LAROSA
;
run;

DATA test1a;
SET test1;
ARRAY ORIGINAL [*] $ 40 C_LN C_FN;

ARRAY NAMES[*] $ 40 LN FN ;
DO I = 1 to 2;
NAMES [I]= ORIGINAL [I];

END;
DO I = 1 to 2;
/* turn anything that is NOT a letter into space*/
NAMES[I] = prxchange("s/[^A-Z]/ /",-1,NAMES[I]);
NAMES[I] = prxchange("s/&BAD_PART/ /",-1, left(trim(NAMES[I])));
NAMES[I] = STRIP(NAMES[I]);
NAMES[I] = COMPBL(NAMES[I]);

END;
DROP I;
RUN;

The results for the last two lines are:

M A R I A R O S A M A R I A D E L R O S A
M A R I A R O S A M A R I A L A R O S A

But I still want a space after getting rid of "DE LA" or "DEL" if they are in the middle of the names.

Thanks.

Re: prxchange with macro

GingerJJ — Fri, 27 Aug 2021 18:46:17 GMT

Actually, there is a space in between each letter for some reason...

@maguiremq @Tom