topic Re: Prxchange to remove string up to and including a word in SAS Enterprise Guide

Prxchange to remove string up to and including a word

Buzzy_Bee — Mon, 03 Aug 2020 09:09:52 GMT

Hello,

I can't find any examples that help me solve my prxchange problem where I want to remove a string up to including the search term. For example, I've got "Good Investments Trust 12 Moon St London" but I want it to search on the word "Trust" and remove everything up to and including Trust, so that I just have "12 Moon St London" remaining. My attempt below returns "Trust 12 Moon St London" so I've only been successful at removing the words before the word Trust, but it still returns the search term itself. Note that the trust names vary in length, so it might be named "Bad Trust" or "A trust with a very long name Trust." Thanks for your help.

data address;
	infile datalines dlm=',' dsd truncover;
	input Mail_addr1 :$30. City :$10. ;
	datalines;
Good Investments Trust 12 Moon St, London
Smith Family Trust 3 Hotel St, Paris
;

data prx;
	set address;
	Address=cats(Mail_addr1, City);
	corrected_address =prxchange("s/.*?(Trust.*)/$1/",1, Address);
drop Mail_addr1 City;
run;

Re: Prxchange to remove string up to and including a word

Shmuel — Mon, 03 Aug 2020 10:21:51 GMT

Why not use next code:

corrected_address = substr(address,find(address,"trust","i")+5);

Re: Prxchange to remove string up to and including a word

andreas_lds — Mon, 03 Aug 2020 10:41:09 GMT

@Buzzy_Bee: is it now and for all times secured that the word "trust" appears only once per observation? What should happen, if there is

Trustowsky Trust 42 Trust Street, Little Rock

in your data?

Re: Prxchange to remove string up to and including a word

hhinohar — Mon, 03 Aug 2020 11:00:27 GMT

Can you clarify if "Trust" and "trust" is a different word?

e.g.

Case:If "trust" is a word that has to be replaced.

Before: After

Good Investments trust 12 Moon St London ---> 12 Moon St London

Case:If "trust" is not

Before: After

Good Investments trust 12 Moon St London ---> (nothing appears)

Re: Prxchange to remove string up to and including a word

Shmuel — Mon, 03 Aug 2020 12:59:46 GMT

If you insist using prx functions, for your specific example, check next code:

data _null_;
  adrs =  "Good Investments Trust 12 Moon St London";
  ad1  = substr(adrs,findw(adrs,"trust","i")+5);
  
  ad2  = prxchange("s/.*?(Trust.*)/$1/",1, adrs);
  prx_id= prxparse("/.*?(Trust.*)/");
  ad3  = prxchange("s/Trust/ /",1,ad2);
  put ad1 = / ad2= / ad3=;
run;

and be aware of all previous given remarks.

Re: Prxchange to remove string up to and including a word

Ksharp — Mon, 03 Aug 2020 12:27:07 GMT

Better using FINDW() .

Re: Prxchange to remove string up to and including a word

Shmuel — Mon, 03 Aug 2020 13:00:56 GMT

You are right, @Ksharp , I have edited my proposed code.

Re: Prxchange to remove string up to and including a word

Ksharp — Mon, 03 Aug 2020 13:20:55 GMT

data address;
	infile datalines dlm=',' dsd truncover;
	input Mail_addr1 :$30. City :$10. ;
	datalines;
Good Investments Trust 12 Moon St, London
Smith Family Trust 3 Hotel St, Paris
;

data prx;
	set address;
	Address=cats(Mail_addr1, City);
	corrected_address =prxchange("s/(.*)(?=\bTrust)//i",1, Address);
drop Mail_addr1 City;
run;

Re: Prxchange to remove string up to and including a word

PGStats — Mon, 03 Aug 2020 14:08:31 GMT

You only need to match the substring that you want to change:

corrected_address = prxchange("s/.*\bTrust\s*//i", 1, Address);

Note The "i" suffix requests case insensitive match.

Re: Prxchange to remove string up to and including a word

Buzzy_Bee — Mon, 03 Aug 2020 19:34:44 GMT

Hi KSharp, your version produces the same problems as mine when I run it.

The result is: "Trust 12 Moon St London" so it still has Trust at the front.

Re: Prxchange to remove string up to and including a word

Shmuel — Mon, 03 Aug 2020 23:25:35 GMT

Next code is not complete but is worth to run and check:

data test;
     input adrs $50.;
cards;
Good Investments Trust 12 Moon St, London
Smith Family Trust 3 Hotel St, Paris
Trustowsky Trust 42 Trust Street, Little Rock
Good Investments trust 12 Moon St London
;
run;

data check;
    obs = _N_;
 set test;
  ad2  = prxchange("s/.*([T]|[t]rust.){1}/$1/",1,adrs);
  prx_id= prxparse("/.*(.{5})/");
  ad3  = prxchange("s/.{5}/ /",1,ad2);
  ad4  = prxchange("s/(.*)(?=\bTrust)//i",1, adrs);   
  drop prx_id;
run;

Re: Prxchange to remove string up to and including a word

Buzzy_Bee — Tue, 04 Aug 2020 08:05:04 GMT

Thank you - that is the right prxchange formula that I was trying to create.

I've got thousands of companies with names like "Very Amazing Investments Trust" and "Serious Lawyers Limited" so this prxchange is exactly what I needed as I can just change the term 'Trust' to 'Limited' and then change it to any of the other company suffixes that appear in the list. Your method means I only need to change the search term once in the formula.

Re: Prxchange to remove string up to and including a word

Buzzy_Bee — Tue, 04 Aug 2020 08:12:42 GMT

Good point Andreas. I should have mentioned that trusts tend to choose sensible names where the term 'trust' only appears once in the name, such as "Brown Family Trust" rather than "A trust with a long name Trust."

However, there are some cases where an address is entered as "C/o The Trustees, Brown Family Trust" so it is definitely possible to see the word trust appear twice in the address.

Re: Prxchange to remove string up to and including a word

whymath — Tue, 04 Aug 2020 14:31:12 GMT

Hi, @PGStats , may I ask how this "s/.*\bTrust\s*//i" works? Is it just like "s/.*\bTrust\s(.*?)//i" ?

Re: Prxchange to remove string up to and including a word

PGStats — Tue, 04 Aug 2020 17:21:48 GMT

Sure, here is how it reads:

.* Match anything, any length

\bTrust Match a word starting with Trust

\s* Match any number of spaces, including none

// Replace the whole matched substring (if any) with nothing

i Make the matching case-insensitive

Re: Prxchange to remove string up to and including a word

whymath — Wed, 05 Aug 2020 13:57:05 GMT

Thanks you.@PGStats