using SCAN

[GreggB]

I inherited an xlsx file with one particularly irksome field -> guardians

data I have:

guardians

Smith, Joe Smith, Jane

Jones, Bill Jones, Pat

Wells, Donna

I want to create a first and last name for each person. This is the code I used:

data pt3;
set pt2;
Lguard_1=scan(guardians,1,',');
Fguard_1=scan(guardians,2,' ');
Lguard_2=scan(guardians,-2,' ');
Fguard_2=scan(guardians,-1,' ');
run;

data I now have:

Lguard_1    Fguard_1     Lguard_2    Fguard_2

Smith              Joe            Smith,          Jane

Jones              Bill             Jones,          Pat

Wells               Donna       Wells,          Donna       

2 things: 1) i need to get rid of the pesky comma in Lguard_2 and 2) I don't want to repeat a single parent/guardian name such as obs 3.

[Tom · Accepted solution]

To properly be able to scan such a field you need something other than SPACE between two names.

Smith, Joe|Smith, Jane

Then you could get the two separate names easily using SCAN().

G1=scan(guardians,1,'|');
G2=scan(guardians,2,'|');

With your current values you could try removing the space after the comma.

G1=scan(tranwrd(guardians,', ',','),1,' ');
G2=scan(tranwrd(guardians,', ',','),2,' ')
Lguard_1=scan(G1,1,',');
Fguard_1=scan(G1,2,',');
Lguard_2=scan(G2,1,',');
Fguard_2=scan(G2,2,',');

But you will still have trouble with first or last names that include a space.

[Kurt_Bremser]

if countc(guardians,",") = 2
then do;
  Lguard_2=compress(scan(guardians,-2,' '),",");
  Fguard_2=scan(guardians,-1,' ');
end;

[Astounding]

As long as you don't have two words as a first name (Jo Ann) or last name (van Gogh), you can do this with slight modification to your code.  You now have:

Lguard_1=scan(guardians,1,',');
Fguard_1=scan(guardians,2,' ');
Lguard_2=scan(guardians,-2,' ');
Fguard_2=scan(guardians,-1,' ');

Instead, use both blanks and commas as delimiters:

Lguard_1=scan(guardians, 1, ' ,');
Fguard_1=scan(guardians, 2, ' ,');
Lguard_2=scan(guardians, 3, ' ,');
Fguard_2=scan(guardians, 4, ' ,');

When there is only one guardian, the third and fourth words will be blank and therefore Lguard_2 and Fguard_2 will also be blank.