DATA Step, Macro, Functions and more

Help with Substrings to Separate First and Last Name

Reply
Frequent Contributor
Posts: 119

Help with Substrings to Separate First and Last Name

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, 8) ;

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

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

Thanks again!

Super User
Posts: 22,843

Re: Help with Substrings to Separate First and Last Name

Posted in reply to ManitobaMoose

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

Frequent Contributor
Posts: 119

Re: Help with Substrings to Separate First and Last Name

Posted in reply to ManitobaMoose
NEVER MIND. I FIGURED IT OUT USING THE SCAN FUNCTION AND PROC SORT. THANKS!
Super User
Posts: 9,560

Re: Help with Substrings to Separate First and Last Name

Posted in reply to ManitobaMoose

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  
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 37

Re: Help with Substrings to Separate First and Last Name

Posted in reply to ManitobaMoose

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;

Super User
Posts: 13,023

Re: Help with Substrings to Separate First and Last Name

Posted in reply to ManitobaMoose

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".

Ask a Question
Discussion stats
  • 5 replies
  • 145 views
  • 4 likes
  • 5 in conversation