BookmarkSubscribeRSS Feed
cmoore
Obsidian | Level 7

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

cmoore
Obsidian | Level 7

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

andreas_lds
Jade | Level 19

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;

 

ChrisBrooks
Ammonite | Level 13

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?

ballardw
Super User

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.

Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1029 views
  • 1 like
  • 6 in conversation