DATA Step, Macro, Functions and more

Cleansing a Raw Persons Name Column

Reply
Contributor
Posts: 32

Cleansing a Raw Persons Name Column

Hi,

 

I have a a column that contains a person's name. There are certain records that conform to the normal way of showing a name such as a forename then a space followed by surname. Other records are back to front where they start with the surname. Please see the data below:

 

HaveWantWant
Raw data nameForenameSurname
AARON KENAVANAARONKENAVAN
BARRON  WILLIAM FWILLIAM BARRON
CHAN  BONNIE YBONNIECHAN
LIN  JAMES CJAMESLIN 
SINCLAIR  PATRICK SPATRICKSINCLAIR
BUTLER  JOHN HJOHNBUTLER
FARBER  MICHAELMICHAELFARBER
HADLEY  JOSEPH PJOSEPHHADLEY
SCHELEIN, ROBERT M.ROBERTSCHELEIN
SARRO, DOUGLAS A.DOUGLASSARRO
PROF. DR. MICHAEL SCHLITTMICHAELSCHLITT
CURTIS.STEFANAKCURTISSTEFANAK
DARA D..MANNDARAMANN

 

Is there a programme available that cleanse the name column so that consistent fornames and surnames can be extracted across all records?

 

Many thanks

Chris

Super User
Super User
Posts: 7,430

Re: Cleansing a Raw Persons Name Column

The question is, how do you know that a word is a first or last name?  Sinclair for instance could be either, much like Lin could be a first name, and James a second name, what logical path indicates that the correct order is James Lin and not Lin James.  So first would come a list of rules, maybe something like:

strip out from string the texts Prof. Dr. ...

if third part of string delimited by spaces is a character then second part of string delimited by spaces is first name and part 1 is second

else first name = scan(string,1), second name=scan(string,2)

 

Once you have the rules, the programming is pretty simple.

Contributor
Posts: 32

Re: Cleansing a Raw Persons Name Column

Originally I had used tranwrd to remove commas. If I left them in the surnames would appear like "BUTLER," so the string would be "BUTLER,  JOHN H", If there is a comma is there a quick way to then rearrange the variables? Thanks

Super Contributor
Posts: 265

Re: Cleansing a Raw Persons Name Column

If the existence of a comma always means that the word on the left of the comma is the lastname and the word on its right side is the first name, than it is easy to extract first- and lastname.

 

untested:

if index(raw, ",") > 0 then do;
  Forename = scan(raw, 2, ",");
  Surname = scan(raw, 1, ",");
end;
else do;
  Forename = scan(raw, 1, " ");
  Surname = scan(raw, 2, " ");
end;

 

Regular Contributor
Posts: 190

Re: Cleansing a Raw Persons Name Column

I don't think it could be done with any certainty - what if you had a name like John James or a non-English language name?

Super User
Posts: 10,550

Re: Cleansing a Raw Persons Name Column

If you can come up with clear rules for parsing names such as:

Jones Elizabeth Ann Brichoux

Ross Raven Aurora-moonlight

 

I'll be impressed. Those are a couple of names I had to work with, also coming in a single field.

I hate dealing with names.

And anyone still collecting names into a single field needs to return their Pong game as it is too modern for them.

Respected Advisor
Posts: 3,908

Re: Cleansing a Raw Persons Name Column

@cmoore

If you've got the SAS Data Quality Server licensed http://support.sas.com/software/products/dataqual/ then you could try code as below.

options dqlocale=(ENUSA);
data want(drop=_:);
  set have;
/*  showTokens=DQPARSEINFOGET('NAME', 'ENUSA');*/
  stdName=dqStandardize(name, 'Name');
  _parsedValue=dqParse(stdName, 'NAME', 'ENUSA');
  nm_prefix=dqParseTokenGet(_parsedValue, 'Name Prefix', 'NAME', 'ENUSA');
  nm_given=dqParseTokenGet(_parsedValue, 'Given Name', 'NAME', 'ENUSA');
  nm_Middle=dqParseTokenGet(_parsedValue, 'Middle Name', 'NAME', 'ENUSA');
  nm_Family=dqParseTokenGet(_parsedValue, 'Family Name', 'NAME', 'ENUSA');
  nm_Suffix=dqParseTokenGet(_parsedValue, 'Name Suffix', 'NAME', 'ENUSA');
  nm_Appendage=dqParseTokenGet(_parsedValue, 'Name Appendage', 'NAME', 'ENUSA');
run;
Ask a Question
Discussion stats
  • 6 replies
  • 214 views
  • 1 like
  • 6 in conversation