topic using SCAN in Statistical Procedures

using SCAN

GreggB — Fri, 17 Dec 2021 19:23:06 GMT

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.

Re: using SCAN

Tom — Fri, 17 Dec 2021 20:12:58 GMT

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.

Re: using SCAN

Kurt_Bremser — Fri, 17 Dec 2021 20:18:49 GMT

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

Re: using SCAN

Astounding — Sat, 18 Dec 2021 04:31:53 GMT

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.