SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Remove last two words in a free text field

Reply
Contributor
Posts: 31

Remove last two words in a free text field

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
Super User
Posts: 10,691

Re: Remove last two words in a free text field

[ Edited ]

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

Re: Remove last two words in a free text field

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)));

PROC Star
Posts: 1,586

Re: Remove last two words in a free text field

data _null_;
x='Hillcrest Avenue Hillcrest Auckland   ';
call scan(x, -2, position, length);
substr(x,position)=' ';
put x=;
run;
Ask a Question
Discussion stats
  • 3 replies
  • 153 views
  • 3 likes
  • 4 in conversation