Try this: (it should work even if there are more than 2 commas... even though you say that won't happen, you get the functionality at no extra charge!)
Data have;
Input Name $ Sex $ units & $100.;
cards;
Amy F 103 ,104
Bob M 203 A
Carolin F 204 B,205B
David M 208 ,210
Emily F 513 C,515
Run;
data want;
set have;
length thisstr $ 100;
units=compress(units);
thisstr='ZZZZ';
count=0;
do while(not missing(thisstr));
count=count+1;
thisstr=scan(units,count,',');
call missing(unit_number);
unit_alpha=' ';
where = anyalpha(thisstr);
if where=0 then unit_number=thisstr;
else do;
unit_number=substr(thisstr,1,where-1);
unit_alpha=substr(thisstr,where);
end;
if not missing(thisstr) then output;
end;
keep name sex unit_number unit_alpha;
run;
So, is this really the way the actual data appears, with inconsistent spacing before the comma (sometimes there is a space and sometimes not) and no indication of a missing value for some records; or is it just poor typing?
Would simply removing the blanks after the person's sex (in the unit number or unit alphabet variable) cause problems?
Can there ever be more than one comma, indicating the result would have to wind up on 3 or more lines?
Try this: (it should work even if there are more than 2 commas... even though you say that won't happen, you get the functionality at no extra charge!)
Data have;
Input Name $ Sex $ units & $100.;
cards;
Amy F 103 ,104
Bob M 203 A
Carolin F 204 B,205B
David M 208 ,210
Emily F 513 C,515
Run;
data want;
set have;
length thisstr $ 100;
units=compress(units);
thisstr='ZZZZ';
count=0;
do while(not missing(thisstr));
count=count+1;
thisstr=scan(units,count,',');
call missing(unit_number);
unit_alpha=' ';
where = anyalpha(thisstr);
if where=0 then unit_number=thisstr;
else do;
unit_number=substr(thisstr,1,where-1);
unit_alpha=substr(thisstr,where);
end;
if not missing(thisstr) then output;
end;
keep name sex unit_number unit_alpha;
run;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.