BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pcfriendly
Calcite | Level 5


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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

9 REPLIES 9
Linlin
Lapis Lazuli | Level 10

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;

Haikuo
Onyx | Level 15

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

pcfriendly
Calcite | Level 5

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

Haikuo
Onyx | Level 15

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

Haikuo
Onyx | Level 15

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.

pcfriendly
Calcite | Level 5

Thanks Hai.kuo,

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

ballardw
Super User

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.

pcfriendly
Calcite | Level 5

Thanks ballardw

Tom
Super User Tom
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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