topic Re: split a full name in SAS Programming

split a full name

axescot78 — Tue, 31 Aug 2021 01:24:06 GMT

I have names that are "last name, first name". Some have a middle initial and some have "Jr". The middle initial is always after the first name separated by a space and the "Jr" is always after the last name separated by a space. How can I split this in 4 different columns? fname, lname, mname, cadency

data have;
input FullName $50.;
datalines;
Smith, John
Smith Jr, John
Smith III, John
Smith, Jane A
Smith, John J
;

Re: split a full name

ChrisNZ — Tue, 31 Aug 2021 02:08:09 GMT

What output do you expect for the input supplied?

Re: split a full name

Reeza — Tue, 31 Aug 2021 02:10:43 GMT

Look up SCAN() function. But how will you tell the difference between cadence and middle name? That one may require you to provide a list of possible values as it can be pretty long. FYI - if you have SAS Data Quality Studio I think it does this for you.

Re: split a full name

axescot78 — Tue, 31 Aug 2021 03:34:52 GMT

@Reeza wrote:
Look up SCAN() function. But how will you tell the difference between cadence and middle name? That one may require you to provide a list of possible values as it can be pretty long. FYI - if you have SAS Data Quality Studio I think it does this for you.

Thanks! Scan worked pretty well. This is what I came up with:

data want;
	set have;
	fname=scan(FullName, 2, ',');
	lname=scan(FullName, 1, ',') ;
	keep FullName fname lname;
run;

data want2;
	set want;
	f_count=countw(fname, ' ');
	l_count=countw(lname, ' ');
	first_name=scan(fname, 1, ' ');
	last_name=scan(lname, 1, ' ');
	if f_count=2 then middle_name=scan(fname, -1, ' ');
		else middle_name=' ';
	if l_count=2 then cadency=scan(lname, -1, ' ');
		else cadency=' ';
	keep FullName first_name middle_name last_name cadency;
run;