topic Re: Cleansing a Raw Persons Name Column in SAS Programming

Cleansing a Raw Persons Name Column

cmoore — Thu, 27 Jul 2017 08:22:43 GMT

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:

Have	Want	Want
Raw data name	Forename	Surname
AARON KENAVAN	AARON	KENAVAN
BARRON WILLIAM F	WILLIAM	BARRON
CHAN BONNIE Y	BONNIE	CHAN
LIN JAMES C	JAMES	LIN
SINCLAIR PATRICK S	PATRICK	SINCLAIR
BUTLER JOHN H	JOHN	BUTLER
FARBER MICHAEL	MICHAEL	FARBER
HADLEY JOSEPH P	JOSEPH	HADLEY
SCHELEIN, ROBERT M.	ROBERT	SCHELEIN
SARRO, DOUGLAS A.	DOUGLAS	SARRO
PROF. DR. MICHAEL SCHLITT	MICHAEL	SCHLITT
CURTIS.STEFANAK	CURTIS	STEFANAK
DARA D..MANN	DARA	MANN

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

Re: Cleansing a Raw Persons Name Column

RW9 — Thu, 27 Jul 2017 08:36:44 GMT

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.

Re: Cleansing a Raw Persons Name Column

ChrisBrooks — Thu, 27 Jul 2017 08:42:49 GMT

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?

Re: Cleansing a Raw Persons Name Column

cmoore — Thu, 27 Jul 2017 08:43:46 GMT

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

Re: Cleansing a Raw Persons Name Column

andreas_lds — Thu, 27 Jul 2017 09:47:55 GMT

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;

Re: Cleansing a Raw Persons Name Column

ballardw — Fri, 28 Jul 2017 23:19:11 GMT

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.

Re: Cleansing a Raw Persons Name Column

Patrick — Sat, 29 Jul 2017 02:12:16 GMT

@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;