Where clause

[amol2939]

I have an lab result containing numerical values and characters
I want to populate only numeric values

[amol2939]

Any idea how to filter the data

[ed_sas_member]

Hi @amol2939 

Here is an approach to achieve this:

data have;
	input lab_result $;
	datalines;
abc
123
def
789
a1
;
run;

data want;
	set have;
	if prxmatch('/^\d+\s*$/',lab_result) then output;
run;

Output:

Capture d’écran 2020-03-18 à 10.16.19.png

-> nb: the column will be still a character one, even if it display only numbers

->the prxmatch function looks for the pattern /^\d+\s*$: , meaning:

A string starting (^) with one or more (+) digits (\d) and zero, one or more (*) trailing blanks (\s) at the end ($) of the string.

Hope this helps,

Best,

[ed_sas_member]

If your data are more like this, here is another solution:

data have;
	infile datalines dlm="," truncover;
	input lab_result :$80.;
	datalines;
abc 123 abc 456
123 abc
def 123
;
run;
data want;
	set have;
	array digits(4);
	do i=1 to countw(lab_result);
		digits(i)=scan(lab_result, i, , 'dko');
    end;
    drop i;
run;

Capture d’écran 2020-03-18 à 10.35.20.png

OR

data have;
	infile datalines dlm="," truncover;
	input lab_result :$80.;
	datalines;
abc 123 abc 456
123 abc
def 123
;
run;
data want;
	set have;
	digits = compress(lab_result,,"dK");
run;

Capture d’écran 2020-03-18 à 10.37.17.png

Best,

[amol2939]

This works but omit the 1.0, 2.30 values

[andreas_lds · Accepted solution]

Or something like:

data want;
  set have;
  if not missing(input(var, ?? best.));
run;

[amol2939]

This code worked
Thanks

[Tom]

Note that when used as an INFORMAT the name BEST is just an alias for the normal numeric informat. Other aliases are F, D and E.
You might also consider testing with COMMA and PERCENT informats also.

[ChrisNZ]

Or this:

If anydigit(VAR);

[Ksharp]

data have;
	input lab_result $;
	datalines;
abc
123
def
789
a1
;
run;
data want;
 set have;
 if notdigit(strip(lab_result)) then delete;
run;