Desktop productivity for business analysts and programmers

If a Field Value is a Char, change to Zero

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

If a Field Value is a Char, change to Zero


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


Accepted Solutions
Solution
‎06-25-2013 10:14 PM
Respected Advisor
Posts: 3,156

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

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.

View solution in original post


All Replies
Super Contributor
Posts: 1,636

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

example:

data have;

input d$;

cards;

wwe

123

;

data want;

set have;

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

proc print;run;

or

data have;

input d$;

cards;

wwe

w23

123

;

data want;

set have;

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

proc print;run;

Respected Advisor
Posts: 3,156

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

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

Frequent Contributor
Posts: 80

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

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?'

Respected Advisor
Posts: 3,156

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

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

Solution
‎06-25-2013 10:14 PM
Respected Advisor
Posts: 3,156

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

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.

Frequent Contributor
Posts: 80

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

Thanks Hai.kuo,

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

Super User
Posts: 11,134

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

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.

Frequent Contributor
Posts: 80

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

Thanks ballardw

Super User
Super User
Posts: 6,848

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

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 );

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 319 views
  • 3 likes
  • 5 in conversation