topic Re: remove character from numeric column in New SAS User

remove character from numeric column

Pooja98 — Fri, 02 Apr 2021 09:14:05 GMT

hi experts,

the dataset i have

column1

2.5

30.59

H 200

87 mg/dL

90 L

28.3

the dataset that i want

column1

2.5

30.59

28.3

if my column contain any variable, i want to set that as missing

TIA

Re: remove character from numeric column

japelin — Fri, 02 Apr 2021 09:23:58 GMT

data have;
  length var $20;
  infile datalines dlm=',';
  input var $;
datalines;
-
2.5
30.59
H 200
87 mg/dL
90 L
28.3
;
run;

data want;
  set have;
  var=ifc(input(var,??best.)=.,'',var);
run;

Re: remove character from numeric column

Pooja98 — Sun, 04 Apr 2021 14:47:30 GMT

Thanks a lot... i got the answer but i still have doubt in this

"var=ifc(input(var,??best.)=.,'',var);"

What it mean: input(var, ??best.)= .

The input function converts character to numeric then what is that "=."........

can you please explain that

Re: remove character from numeric column

Tom — Sun, 04 Apr 2021 15:07:25 GMT

@Pooja98 wrote:

Thanks a lot... i got the answer but i still have doubt in this

"var=ifc(input(var,??best.)=.,'',var);"

What it mean: input(var, ??best.)= .

The input function converts character to numeric then what is that "=."........

can you please explain that

Since your variable is character this code is using the INPUT() function to check it the value can be converted into a number. When it can it sets the the variable back to itself otherwise it sets it to blanks.

The ?? before the informat will suppress any error messages about strings that cannot be converted into a number.

You can make it less confusing by just using an IF/THEN statement instead of the confusing IFC() function.

You can make the test less confusing also by not trying to use the name of a FORMAT in the place where SAS is expecting the name of an INFORMAT. If you reference BEST as an INFORMAT it is just an alias for the normal numeric informat.

You can also use the MISSING() function instead of just comparing the INPUT() result to the numeric missing value. In addition to being more readable for humans this will also catch the issue where value like E being converted to special missing .E which is not equal to . but will be considered missing by the MISSING() function.

if missing(input(var,??32.)) then var=' ';

Note also that if you wanted to create an actual numeric variable you will have to give that variable a new name. You cannot change the type of an existing variable. In that case the code is even easier (clearer).

So if have an existing dataset named HAVE with a character variable named STR and you want to create a new variable name NUM that is numeric then use:

data want;
  set have;
  num = input(str,??32.);
run;

If you would like to see messages in the log when the value of STR could not be converted to a number then remove the ?? from before the informat.

Re: remove character from numeric column

japelin — Mon, 05 Apr 2021 00:44:18 GMT

Thank you for your explanation, @Tom .
I also noticed after posting that ifc would cause unnecessary processing, so I think a simple if then statement would be better.