topic Re: Remove last two words in a free text field in SAS Data Management

Remove last two words in a free text field

eemrun — Wed, 18 Apr 2018 12:09:28 GMT

I have addresses that I need to separate as street address. The last two words in the address field is usually the region and city. Any ideas?

Before
Hillcrest Avenue Hillcrest Auckland

After
Hillcrest Avenue

Re: Remove last two words in a free text field

Ksharp — Wed, 18 Apr 2018 12:55:12 GMT


data _null_;
x='Hillcrest Avenue Hillcrest Auckland   ';
y=prxchange('s/\w+\s+\w+$//',1,strip(x));
put x= y=;
run;

Re: Remove last two words in a free text field

Astounding — Wed, 18 Apr 2018 12:48:32 GMT

In practice, you might want to check that you have more than 2 words in the address, before doing this:

address = substr(address, 1, length(address) - 2 - length(scan(address, -1)) - length(scan(address, -2)));

Re: Remove last two words in a free text field

novinosrin — Wed, 18 Apr 2018 15:19:35 GMT

data _null_;
x='Hillcrest Avenue Hillcrest Auckland   ';
call scan(x, -2, position, length);
substr(x,position)=' ';
put x=;
run;