A typical strategy:
data wantList;
set have;
stringId = _n_;
do compound =
"Perfluorobutanesulfonic Acid",
"Perfluoroheptanoic Acid",
"Perfluorohexanesulfonic Acid";
pos = find(text, compound, "it");
if pos > 0 then do;
pos = pos + length(compound);
length var $26; /* 32 - length("_units") */
var = scan(compound, 1);
value = input(scan(substr(text, pos), 1, " "), best.);
units = scan(substr(text, pos), 2, " ");
output;
end;
end;
drop text pos;
run;
proc transpose data=wantList out=wantvalues(drop=_name_) suffix=_value;
by stringId;
var value;
id var;
run;
proc transpose data=wantList out=wantunits(drop=_name_) suffix=_units;
by stringId;
var units;
id var;
run;
data want;
merge wantValues wantUnits;
by stringId;
drop stringId;
run;
You need two transpose steps because values are numeric and units are character.
... View more
