topic Re: If a Field Value is a Char, change to Zero in SAS Enterprise Guide

If a Field Value is a Char, change to Zero

pcfriendly — Wed, 26 Jun 2013 00:19:20 GMT

Hi,

I have a field that contains both numbers & text.

Where the value is text, I want those values to be changed to a zero.

I'm trying to think of a function to use in an Advanced Expression to do this.

Can anyone suggest what I should use? Thanks

Re: If a Field Value is a Char, change to Zero

Linlin — Wed, 26 Jun 2013 00:45:36 GMT

example:

data have;

input d$;

cards;

wwe

123

;

data want;

set have;

if anydigit(d)=0 then d='0';

proc print;run;

data have;

input d$;

cards;

wwe

w23

123

;

data want;

set have;

if anyalpha(d) then d='0';

proc print;run;

Re: If a Field Value is a Char, change to Zero

Haikuo — Wed, 26 Jun 2013 01:06:03 GMT

Or if you hence want to convert it to number:

data have;

input d$;

cards;

wwe

123

;

data want;

set have;

num=sum(input(d,??best.),0);

run;

Haikuo

Re: If a Field Value is a Char, change to Zero

Tom — Wed, 26 Jun 2013 01:37:58 GMT

You will need to create a new variable to store a numeric value. You can use INPUT() function to convert the character variable to a number and use the COALESCE() to convert the missing values to zero.

numvar = coalesce( input( charvar, ??32. ), 0 );

Re: If a Field Value is a Char, change to Zero

pcfriendly — Wed, 26 Jun 2013 01:42:25 GMT

Thanks Hai.Kuo,

I should have mentioned I'm using the query builder & creating an Advanced Expression.

Is it possible to advise an expression to do this?'

Re: If a Field Value is a Char, change to Zero

Haikuo — Wed, 26 Jun 2013 01:58:17 GMT

I see no reason you can't do it in query builder, Try to 'add' a 'calculated' variable, and apply the function needed.

Re: If a Field Value is a Char, change to Zero

Haikuo — Wed, 26 Jun 2013 02:14:11 GMT

I have just tried on EG 4.3, there will be minor modification.

Query Builder --- Computed Columns --- New --- Advanced expression --- Enter an expression as: sum(input(d,best.),0) , please note that ?? have been removed (for unknown reason, EG does not like it), and d is your original variable. --- follow the instructions till the end.

HTH,

Haikuo

Update: figure out the reason why EG doesn't like ??, actually it is Proc SQL , Query Builder will not generate data step, instead, it uses Proc SQL.

Re: If a Field Value is a Char, change to Zero

pcfriendly — Wed, 26 Jun 2013 21:45:17 GMT

Thanks Hai.kuo,

That works perfectly, can you explain what 'best' is doing? Thanks

Re: If a Field Value is a Char, change to Zero

ballardw — Wed, 26 Jun 2013 22:39:50 GMT

The BEST. is used as an informat to read the value of the variable D without asuming anything about how big/small the variable may be or number of decimal places. If D is not a numeric value then the input(d,best.) returns a missing value. In which case the sum of missing and 0 returns as 0.

Re: If a Field Value is a Char, change to Zero

pcfriendly — Wed, 26 Jun 2013 22:43:21 GMT

Thanks ballardw