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
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.
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
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;
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?
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.