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.
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.
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.
if countc(guardians,",") = 2
then do;
Lguard_2=compress(scan(guardians,-2,' '),",");
Fguard_2=scan(guardians,-1,' ');
end;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.