07-27-2017 04:22 AM
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:
|Raw data name||Forename||Surname|
|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|
|HADLEY JOSEPH P||JOSEPH||HADLEY|
|SCHELEIN, ROBERT M.||ROBERT||SCHELEIN|
|SARRO, DOUGLAS A.||DOUGLAS||SARRO|
|PROF. DR. MICHAEL SCHLITT||MICHAEL||SCHLITT|
Is there a programme available that cleanse the name column so that consistent fornames and surnames can be extracted across all records?
07-27-2017 04:36 AM
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.
07-27-2017 04:43 AM
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
07-27-2017 05:47 AM
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.
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;
07-28-2017 07:19 PM
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.
07-28-2017 10:12 PM
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;