Parsing a character string based on format

Walternate — Fri, 06 Mar 2020 15:22:36 GMT

Hi all,

I have a very, very large dataset with a variable that should hold full names. The names should be formatted as Last, First (separated by a comma) or First Last (separated by a space), but there is dirt. There can also be middle names after the first names in both of those examples:

John Smith

Dan Fred Jones

Doe, Jane

Hamilton, Diane A

I'm trying to do a bunch of cleaning and I could use some help with how to set up one of the more complex steps.

Since in the data, last names are much, much more likely to be hyphenated than first names, I'm applying the following rules:

1. If a value has one (and only one) hyphen, check to see the format of the value

2. If the value has a comma in it, the hyphenated word should become the first word in the string (last name position) if it is not already

3. If the value has no comma, the hyphenated word moves to the end of the string if it is not already (last name position)

Values with 0 hyphens, or >1 hyphen, should be left intact.

Example:

before

Jane Doe

Smith-John-F

Jones-Anderson, Dan

Kate, Simons-Hunt

Parker-Parks Ashley

after

Jane Doe

Smith-John-F

Jones-Anderson, Dan

Simons-Hunt, Kate

Ashley Parker-Parks

The first two do not have one and only one hyphen so they are left alone. The third has the hyphenated name in the correct location already, so it is left alone. The next one (in red) has only one hyphen, so it needs to be cleaned. The value has a comma, so the hyphenated name should be the first word in the string, which it is not. This value would need to be cleaned. Similarly, the last record only has one hyphen. It has no comma so the hyphenated word should be the last word in the string, which it is not. This value would need to be cleaned.

Any help is much appreciated. I haven't been able to figure out an approach yet, so I can't provide what I have so far.

Re: Parsing a character string based on format

Duggins — Fri, 06 Mar 2020 16:17:31 GMT

Since those with (0, >1) hyphens are to be left alone, I check for that first. After that, just check for a comma and position of the hyphen relative to the comma. If there is no comma, check for the position of the hyphen relative to the space. I've only reassigned the values when necessary.

data have;
  infile cards truncover;
  input Name $300.;
  cards;
Jane Doe
Smith-John-F
Jones-Anderson, Dan
Kate, Simons-Hunt
Parker-Parks Ashley
  ;
run;

data want;
  set have;
  if countc(name,'-') eq 1 then do;
      if countc(name,',') eq 1 and countc(scan(name,1,','),'-') eq 0 
          then name = catx(', ',scan(name,2,','),scan(name,1,','));
        else if countc(name,',') eq 0 and countc(scan(name,2,' '),'-') eq 0 
            then name = catx(' ',scan(name,2,' '),scan(name,1,' '));
    end;
run;

Re: Parsing a character string based on format

Walternate — Fri, 06 Mar 2020 16:39:04 GMT

That worked, thank you!

topic Re: Parsing a character string based on format in SAS Programming

Parsing a character string based on format

Re: Parsing a character string based on format

Re: Parsing a character string based on format