BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
teamlinerek
Fluorite | Level 6

I HAVE the following data:

FirstName   MiddleName    LastName

John                  Bruce                  Wayne

Sylvia                  Doe

Ronald                M.                        Donald

 

I am wanting to align all of the last names by replacing LastName with the MiddleName. I WANT the following data set:

FirstName   MiddleName    LastName

John                  Bruce                  Wayne

Sylvia                                               Doe

Ronald                M.                        Donald

 

Thank you, in advance, for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

A straightforward way:

 

data want;
   set have;
   if LastName = " " then do;
      LastName = MiddleName;
      MiddleName = " ";
   end;
run;

View solution in original post

2 REPLIES 2
Astounding
PROC Star

A straightforward way:

 

data want;
   set have;
   if LastName = " " then do;
      LastName = MiddleName;
      MiddleName = " ";
   end;
run;
ballardw
Super User

Just for fun, apply your logic to the name "Moon Flower Star Blossom" which I ran into in a data source with single name field and had to attempt to determine First Last and Middle.

You may want to look at other names that include spaces "Le Blanc" "von Trappe" and others.

Depending on your source you may also get to look for : Jr (or Junior), Second, Third .. II III IV and decide if that is actually name or descriptor, Dr or Doctor, Esq (esquire though that has fallen from popularity) other more "title" than name elements, and nick names set off in parentheses : "Robert (Bob) Michael Smith" for example.

 

If you have a large number of names I would use a COUNTW function to provide a warning for any names that have more than 3 words as they are very likely to require more manual intervention. Hint: if you go this way specify the delimiters for the Countw as some names contain hyphens and would be treated as a delimiter between words by default.

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!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 615 views
  • 2 likes
  • 3 in conversation