Move cell data to adjacent column

[teamlinerek]

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!

[Astounding · Accepted solution]

A straightforward way:

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

[ballardw]

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.