BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Walternate
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Add a space after every comma.  Replace multiple spaces with one space. Remove space before comma.

want=tranwrd(compbl(tranwrd(have,',',', ')),' ,',',');

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

Add a space after every comma.  Replace multiple spaces with one space. Remove space before comma.

want=tranwrd(compbl(tranwrd(have,',',', ')),' ,',',');
novinosrin
Tourmaline | Level 20

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
Ksharp
Super User
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;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1663 views
  • 1 like
  • 4 in conversation