Help with Substrings to Separate First and Last Name

[ManitobaMoose]

Hi,

Everyone on here has been very helpful.

I am trying to organize the data below by last name, in alphabetical order. My thinking is to use the substr function, but the first name is not the same length for each observation. Therefore, when I use substring, either the first name or last name is cut off. I could really use some ideas on how to deal with this issue. Thanks so much!

Here is the original data:

data learn.names_and_more;
   input Name $20.
         Phone & $14.
         Height & $10.
         Mixed & $8.;
datalines;
Roger Cody        (908)782-1234  5ft. 10in.  50 1/8
Thomas Jefferson   (315) 848-8484  6ft. 1in.  23 1/2
Marco Polo          (800)123-4567  5Ft. 6in.  40
Brian Watson        (518)355-1766  5ft. 10in  89 3/4
Michael DeMarco     (445)232-2233  6ft.       76 1/3
;

Below is my idea, which does not work:

libname Learn '/folders/myfolders/Learn' ;
Data FirstLastName ;
    Set learn.names_and_more ;
        Firstname = substr(Name, 1,7) ;

        Lastname = substr(Name, 😎 ;

        
        
proc print Data=FirstLastname noobs ;
    var FirstName  LastName ;
run ;

---------------------------

Thanks again!

[Reeza]

If it’s just first and last name, try the SCAN() function instead. 

[ManitobaMoose]

NEVER MIND. I FIGURED IT OUT USING THE SCAN FUNCTION AND PROC SORT. THANKS!

[Kurt_Bremser]

For posting code, always (and I mean ALWAYS!) use the coding subwindow, available either through the {i} or "little running man" icon (see https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce)

The main posting window invariably scrambles code by omitting blanks etc.

I had to fiddle for some time just to get your example data step to produce usable output.

Try the scan() function instead of substr():

data names_and_more;
infile datalines truncover;
input
  Name $20.
  Phone  $14.
  Height  $10.
  Mixed $8.
;
datalines;
Roger Cody          (908)782-1234 5ft. 10in.50 1/8
Thomas Jefferson    (315) 848-84846ft. 1in. 23 1/2
Marco Polo          (800)123-4567 5Ft. 6in. 40
Brian Watson        (518)355-1766 5ft. 10in 89 3/4
Michael DeMarco     (445)232-2233 6ft.      76 1/3
;
run;

data FirstLastName;
set names_and_more;
Firstname = scan(Name,1);
Lastname = scan(Name,2);
run;

proc print data=FirstLastname noobs;
var FirstName LastName;
run;

Result:

Firstname    Lastname

 Roger       Cody     
 Thomas      Jefferson
 Marco       Polo     
 Brian       Watson   
 Michael     DeMarco  

[soham_sas]

Hi

you can do it by using the substrng function also .

please find the below code

data names_and_more;
   input Name $20.
         Phone & $14.
         Height & $10.
         Mixed & $8.;
datalines;
Roger Cody          (908)782-1234  5ft. 10in.  50 1/8
Thomas Jefferson    (315) 848-8484 6ft. 1in.   23 1/2
Marco Polo          (800)123-4567  5Ft. 6in.   40
Brian Watson        (518)355-1766  5ft. 10in   89 3/4
Michael DeMarco     (445)232-2233  6ft.        76 1/3
;
run;

proc sql;
create table abc as select * ,  substr(Name, 1,find(name," ")) as firstname , substr(Name,find(name," ")) as lastname
from names_and_more
order by lastname;
quit;

[ballardw]

In anything holding name data in one field I would always include a check for more than two "names" appearing in the data.

Your data may actually have middle initials, compound first names like "Billy Bob Jones" or compound last names like "Jean Le Blanc"

"Carl von Someplace". And you data entry people may have something like "John Jr Jones" or other name elements such as "MS Ella Smith".