topic Re: Formatting comma values in SAS Programming

Formatting comma values

Walternate — Tue, 24 Mar 2020 13:25:46 GMT

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,

Re: Formatting comma values

Tom — Tue, 24 Mar 2020 13:52:21 GMT

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

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

Re: Formatting comma values

novinosrin — Tue, 24 Mar 2020 14:22:58 GMT

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

Re: Formatting comma values

Ksharp — Wed, 25 Mar 2020 12:04:00 GMT

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;