topic Re: Help with Substrings to Separate First and Last Name in SAS Programming

Help with Substrings to Separate First and Last Name

ManitobaMoose — Tue, 28 Nov 2017 05:50:53 GMT

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!

Re: Help with Substrings to Separate First and Last Name

Reeza — Tue, 28 Nov 2017 05:54:42 GMT

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

Re: Help with Substrings to Separate First and Last Name

ManitobaMoose — Tue, 28 Nov 2017 05:57:39 GMT

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

Re: Help with Substrings to Separate First and Last Name

Kurt_Bremser — Tue, 28 Nov 2017 07:44:06 GMT

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

Re: Help with Substrings to Separate First and Last Name

soham_sas — Tue, 28 Nov 2017 08:27:29 GMT

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;

Re: Help with Substrings to Separate First and Last Name

ballardw — Tue, 28 Nov 2017 15:59:32 GMT

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