topic Converting character to numeric in New SAS User

Converting character to numeric

bmenter — Sat, 01 Dec 2018 17:12:20 GMT

Hello, I am trying to convert a character variable to a numeric variable. I used the INPUT function in the advanced expression builder, but the data that is currently a character field has commas and parenthesis in it. My guess is that I need to remove any special characters prior to converting the data to numeric.

I can't change the field on import because I have to transpose the data to get it in the correct order.

INPUT(t1.'Total Pre-Tax Book Income:'n,BEST12.)

Any help would be great! I am a very new user to this program.

Brooke

Re: Converting character to numeric

VDD — Sat, 01 Dec 2018 18:57:34 GMT

I find that if I create a dummy like this I am able to convert the numbers into character

data have(rename=(myvar2=myvar));

set have

myvar2 = input(myvar, best12.);

drop myvar;

run;

Re: Converting character to numeric

Tom — Sat, 01 Dec 2018 20:29:50 GMT

Try using the COMMA informat instead.

Otherwise use COMPRESS() function to remove the characters you don't want.

input(compress(xxx,'.','kd'),32.)

Examples:

data test;
  input char $32.;
  num1=input(char,??32.);
  num2=input(char,??comma32.);
  num3=input(compress(char,'.','kd'),??32.);
cards;
123
1.3
$123
123,456
(123,456)
;

Obs    char          num1         num2        num3

 1     123          123.0        123.0       123.0
 2     1.3            1.3          1.3         1.3
 3     $123            .         123.0       123.0
 4     123,456         .      123456.0    123456.0
 5     (123,456)       .     -123456.0    123456.0

Re: Converting character to numeric

bmenter — Mon, 03 Dec 2018 13:20:05 GMT

Hello, thank you for your solution, can you tell me what I am doing wrong? I lose the negative sign for negative values? Thank you!! I attached a screen shot of the results.

PROC SQL;
CREATE TABLE WORK.OTP_PBT_AND_TAX_GRP AS
SELECT t1.Source,
t1.'Total Total Pre-Tax Book Income'n,
t1.'Total Tax Provision'n,
/* PTBI */
(INPUT(COMPRESS(t1.'Total Total Pre-Tax Book Income'n, ' . ' , 'kd'),BEST32.)) FORMAT=BEST32. AS PTBI,
/* PTBI1 */
(INPUT(t1.'Total Total Pre-Tax Book Income'n, BESTcomma32.)) AS PTBI1
FROM WORK.TRNSTRANSPOSED_0000 t1;
QUIT;

Re: Converting character to numeric

Tom — Wed, 05 Dec 2018 13:28:37 GMT

Include hyphen along with period in the list of characters to keep.

But if you want to read the parentheses as meaning negative value then use the COMMA informat.