Hi,
I have data with a name variable which is usually (but not always) formatted Last, First Middle. I want to do a little cleaning to make sure that for those values with a comma, there is no space between Last and the comma, and there is one (and only one) space between the comma and First.
Name
Smith,John B
Doe, Jane M
Jones , Jim
Values can have 0 to multiple spaces both before and after the comma, so all these scenarios need to be accounted for.
The desired output would be:
Name
Smith, John B
Doe, Jane M
Jones, Jim
ie, last name, then comma (no space between), then a single space, then first, then middle.
I was thinking of using tranwrd to replace space+comma with just comma, and comma+multiple spaces/zero spaces with comma+one space, but since there can be multiple spaces, there'd have to be some sort of iteration/passing through mulitple times and I couldn't figure out how to set that up.
Any help is much appreciated.
Thanks,
Add a space after every comma. Replace multiple spaces with one space. Remove space before comma.
want=tranwrd(compbl(tranwrd(have,',',', ')),' ,',',');
Add a space after every comma. Replace multiple spaces with one space. Remove space before comma.
want=tranwrd(compbl(tranwrd(have,',',', ')),' ,',',');
Hi @Walternate Whilst the known fact being sage Tom's solution of course slick, smart and fast, I wanted to try a solution using Regular expression.
data have;
input Name $20.;
cards;
Smith,John B
Doe, Jane M
Jones , Jim
;
data want;
set have;
new_name= prxchange('s/(\w+)\s*,\s*(\w+)\s*(\w+)?/$1, $2 $3/', -1, name);
run;
Name | new_name |
---|---|
Smith,John B | Smith, John B |
Doe, Jane M | Doe, Jane M |
Jones , Jim | Jones, Jim |
data have;
input Name $20.;
cards;
Smith,John B
Doe, Jane M
Jones , Jim
;
data want;
set have;
new_name= left(prxchange('s/\b(\w+)\b/ $1 /', -1, name));
run;
proc print;run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.