split a full name

[axescot78]

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
;

[ChrisNZ]

What output do you expect for the input supplied?

[Reeza]

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.

[axescot78 · Accepted solution]

---

@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;