topic Re: Where clause in SAS Programming

Where clause

amol2939 — Wed, 18 Mar 2020 09:07:33 GMT

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

Re: Where clause

amol2939 — Wed, 18 Mar 2020 09:10:21 GMT

Any idea how to filter the data

Re: Where clause

ed_sas_member — Wed, 18 Mar 2020 09:26:48 GMT

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:

-> 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,

Re: Where clause

andreas_lds — Wed, 18 Mar 2020 09:27:47 GMT

Or something like:

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

Re: Where clause

ed_sas_member — Wed, 18 Mar 2020 09:37:27 GMT

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;

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;

Best,

Re: Where clause

ChrisNZ — Wed, 18 Mar 2020 09:36:53 GMT

Or this:

If anydigit(VAR);

This code worked Thanks

amol2939 — Wed, 18 Mar 2020 09:55:12 GMT

This code worked
Thanks

This works but omit the 1.0, 2.30 values

amol2939 — Wed, 18 Mar 2020 09:58:02 GMT

This works but omit the 1.0, 2.30 values

Re: Where clause

Ksharp — Wed, 18 Mar 2020 11:08:20 GMT

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

Re: Where clause

Tom — Wed, 18 Mar 2020 13:00:59 GMT

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.