Extracting values from a string

[tallkell]

Hi,

I have 8 observations of clinical results expresed in expenential counts (e.g., 1.94E+4) plus their corresponding log values within parentheses, e.g., (4.29) in a chracter string. I would like to parse this string and extract the two sets of values into two separate variables (counts, logCounts). I think a perl or scan function can help me with this. A "no results" would also be carried forward to the variable.

string                             wanted result: Counts          wanted result: logCounts

"1.94E+4 (4.29)"            1.94E+4                               4.29
"5.54E+4 (4.74)"            5.54E+4                              4.74
"1.19E+4 (4.08)"            1.19E+4                              4.08
"2.76E+4 (4.44)"             2.76E+4                             4.44
"8.29E+4 (4.92)"             8.29E+4                             4.92
"< 2.00E+1 (1.30)"          <2.00E+1                           1.30
"2.99E+5 (5.48)"             2.99E+5                              5.48

"No results found"           No results found                 No results found

I am running SAS 9.4 on Windows 7. 

Thanks

--tim

[RW9 · Accepted solution]

Post test data in the form of a datastep!

As such this code is untested, and I assume the two wants are string:

data want;
  set have;
  counts=scan(string,1,"(");
  logcounts=compress(scan(string,2,"("),")");
run;

[PeterClemmensen]

Like this?

data have;
input string $20.;
infile datalines dlm = ',';
datalines;
1.94E+4 (4.29)
5.54E+4 (4.74)
1.19E+4 (4.08)
2.76E+4 (4.44)
8.29E+4 (4.92)
<2.00E+1 (1.30)
2.99E+5 (5.48)
No results found
;

data want;
	set have;
	
	if string = 'No results found' then do;
		Wanted_Counts = string;
		Wanted_LogCounts = string;
	end;

	else do;
		Wanted_Counts = scan(string,1,' ');
		Wanted_LogCounts = compress(compress(scan(string,2,' '),'(',),')');
	end;
run;