topic Re: Remove symbols from a character field and make it numeric in SAS Programming

Remove symbols from a character field and make it numeric

Jagadeesh2907 — Tue, 11 Sep 2018 15:14:25 GMT

I have a dataset DST1 which has two columns (ID and AMOUNT) and both are character field.

ID AMOUNT

A1 +100

A2 0

A3 +350

A4 -200

I am trying to convert the Amount column as number and remove the + sign for positive numbers and retain the sign for negative number only. Could you please help.

Re: Remove symbols from a character field and make it numeric

novinosrin — Tue, 11 Sep 2018 15:23:47 GMT

data have;
input ID $    AMOUNT $;
cards;
A1    +100
A2     0
A3     +350
A4     -200
;

data want;
set have;
New_amt=input(compress(amount,,'kd'),8.)*sign(amount);
run;

Re: Remove symbols from a character field and make it numeric

novinosrin — Tue, 11 Sep 2018 15:51:09 GMT

data have;
input ID $    AMOUNT $;
cards;
A1    +100
A2     0
A3     +350
A4     -200
;

data want;
set have;
_l=length(amount);
_infmt=cats('bz',_l,'.');
new_amount=inputn(amount,_infmt);
drop _:;
run;

However, not confident of this solution though

Re: Remove symbols from a character field and make it numeric

rajeshalwayswel — Tue, 11 Sep 2018 16:35:10 GMT

  data have;
 input ID $    AMOUNT $;
 cards;
 A1    +100
 A2     0
 A3     +350
 A4     -200
 ;

 data want;
 set have;
 New_amt=input(compress(amount,'-','kd'),best.);

 run;

Re: Remove symbols from a character field and make it numeric

novinosrin — Tue, 11 Sep 2018 16:37:28 GMT

Most simplest:

data have;
input ID $    AMOUNT $;
cards;
A1    +100
A2     0
A3     +350
A4     -200
;

data want;
set have;
new_amt=input(amount,e8.);
run;

I only learned e informat just now

Re: Remove symbols from a character field and make it numeric

ballardw — Tue, 11 Sep 2018 16:57:23 GMT

An existing variable cannot change types. To create a numeric value you will need to create a new variable.

If you want to use the same name of the variable as the new variable name then modify @novinosrin solution as

data want;
   set have (rename=(amount=oldamount));
   new_amt=input(oldamount,e8.);
   drop oldamount;
run;

Re: Remove symbols from a character field and make it numeric

Ksharp — Wed, 12 Sep 2018 13:43:42 GMT

data have;
input ID $    AMOUNT $;
cards;
A1    +100
A2     0
A3     +350
A4     -200
;

data want;
set have;
New_amt=input(amount,best.);
run;