Hi All,
I want to extract people's first name where the full name starts with one of the following
Mr,
Mr.,
Mrs,
Mrs.,
Dr,
Dr.
Please note that some name doesn't start with their Title. So far I was able to work with the Title part. But I need help with the name part.
DATA All;
INPUT Fullname $40.;
CARDS;
Mr. Simon Hill
Mr ABBY ANTAL
Tom Kewen
Miss Ann Sharp
Miss. Carla Newman
Mrs SYLVIA TUYTEL
Aleya Khan
Mrs. Melissa Ward
Dr. Cobi Sharpe;
RUN;
DATA SShuffle.All3;
LENGTH Title Fullname2 $ 40.;
SET SShuffle.All2;
IF fullname=: 'Mr.' THEN Title=SCAN(Fullname,1)||'.';
IF fullname=: 'Mr ' THEN Title=SCAN(Fullname,1)||'.';
IF fullname=: 'Mrs.' THEN Title=SCAN(Fullname,1)||'.';
IF fullname=: 'Mrs ' THEN Title=SCAN(Fullname,1)||'.';
IF fullname=: 'Miss.' THEN Title=SCAN(Fullname,1)||'.';
IF fullname=: 'Miss ' THEN Title=SCAN(Fullname,1)||'.';
IF fullname=: 'Dr.' THEN Title=SCAN(Fullname,1)||'.';
IF fullname=: 'Dr ' THEN Title=SCAN(Fullname,1)||'.';
RUN;
Output table:
Title Name
Mr. Simon Hill
Mr ABBY ANTAL
Tom Kewen
Miss Ann Sharp
Miss. Carla Newman
Mrs SYLVIA TUYTEL
Aleya Khan
Mrs. Melissa Ward
Dr. Cobi Sharpe
Can someone please help.
DATA All;
INPUT Fullname $40.;
CARDS;
Mr. Simon Hill
Mr ABBY ANTAL
Tom Kewen
Miss Ann Sharp
Miss. Carla Newman
Mrs SYLVIA TUYTEL
Aleya Khan
Mrs. Melissa Ward
Dr. Cobi Sharpe
;
RUN;
data want;
set all;
length title $5 name $50;
IF fullname in : ('Mr.','Mr','Mrs.','Mrs ','Miss.', 'Miss ','Dr.','Dr ') THEN do;
Title=SCAN(Fullname,1)||'.';
name=substr(fullname,anyspace(fullname)+1);
end;
else name=Fullname;
run;
Explain further.
Do the names ALWAYS begin with one of these abbreviations? Or do some names begin with the person's name and no abbreviation? Do some names begin with a different abbreviation not on your list, what should we do then?
Give us some rules (in words) that we can turn into code.
DATA All;
INPUT Fullname $40.;
CARDS;
Mr. Simon Hill
Mr ABBY ANTAL
Tom Kewen
Miss Ann Sharp
Miss. Carla Newman
Mrs SYLVIA TUYTEL
Aleya Khan
Mrs. Melissa Ward
Dr. Cobi Sharpe
;
RUN;
data want;
set all;
length title $5 name $50;
IF fullname in : ('Mr.','Mr','Mrs.','Mrs ','Miss.', 'Miss ','Dr.','Dr ') THEN do;
Title=SCAN(Fullname,1)||'.';
name=substr(fullname,anyspace(fullname)+1);
end;
else name=Fullname;
run;
Adding
My solution but not the solution from @novinosrin will fail if someone's name is not preceded by an abbreviation, and the person's name begins with Miss, such as Missy Taylor. I'm sure there is a simple adjustment to my code that will fix this, but not needed as there is a solution that doesn't have this problem.
data all3;
length title $ 12 fullname name $ 40;
set all;
fullname=compress(fullname,'.');
if fullname=: 'Mr' then do;
title=scan(fullname,1)||'.';
name=substr(fullname,anyspace(fullname)+1);
end;
else IF fullname=: 'Miss' THEN do;
Title=SCAN(Fullname,1)||'.';
name=substr(fullname,anyspace(fullname)+1);
end;
else IF fullname=: 'Dr' THEN do;
Title=SCAN(Fullname,1)||'.';
name=substr(fullname,anyspace(fullname)+1);
end;
else name=fullname;
run;
This method will fail for other abbreviations before the person's name, such as Msgr.
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.