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
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.
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;
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
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?'
I see no reason you can't do it in query builder, Try to 'add' a 'calculated' variable, and apply the function needed.
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.
Thanks Hai.kuo,
That works perfectly, can you explain what 'best' is doing? Thanks
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.
Thanks ballardw
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 );
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.