Formatting comma values

[Walternate]

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,

[Tom · Accepted solution]

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

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

[novinosrin]

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]

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;