BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mlogan
Lapis Lazuli | Level 10

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.

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
mlogan
Lapis Lazuli | Level 10
Hi PaigeMillar, Yes some name doesn't start with Title. I modified my post. Would you please read again. Thanks.
novinosrin
Tourmaline | Level 20

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;
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
PaigeMiller
Diamond | Level 26
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.

--
Paige Miller

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 5 replies
  • 2368 views
  • 4 likes
  • 3 in conversation