topic convert character to numeric in SAS Programming

convert character to numeric

echoli — Wed, 31 May 2017 22:23:31 GMT

Hi All,

How to convert character variables to numeric? I have data in excel like below:

4.7479438e+01

3.4455325e+01

2.5549948e+01

2.0816337e+01

2.2582412e+01

my code in sas is

data new;
set old;
numeric = input(col1,10.6);
run;

but it doesn't work. The numeric column is missing values.

Any idea???

Thanks,

Re: convert character to numeric

Reeza — Wed, 31 May 2017 22:27:11 GMT

It should be read by default. What's the variable type/format? When you use INPUT() try using best32. Informat.

Re: convert character to numeric

Astounding — Wed, 31 May 2017 22:53:10 GMT

The 10.6 informat is wrong. That tells SAS to read 10 characters from your incoming character string. Since the 10th character is a plus sign, you get missing values (along with a message in the log about invalid data). Instead of 10.6, use:

numeric = input(col1, 13.);

If you actually have some longer strings in the data, you can use a wider informat. Do not specify positions after the decimal point (such as 13.6). Again, once you understand what that means, you'll see it can produce the wrong result.

Re: convert character to numeric

MikeZdeb — Wed, 31 May 2017 23:26:05 GMT

Hi ... it's the WIDTH that's the problem. Anyththing less than 13 will not work (COL1 has 13 characters).

If data being read with a W.D style informat already has a decimal point, SAS will ignore the D portion of a W.D informat.

data old;
input col1 :$13. @@;
datalines;
4.7479438e+01 3.4455325e+01 2.5549948e+01 2.0816337e+01 2.2582412e+01
;

data new;
set old;
num1 = input(col1,10.6);
num2 = input(col1,13.6);
num3 = input(col1,13.);
num4 = input(col1,30.6);
num5 = input(col1,30.);
run;

DATA SET: new

col1 num1 num2 num3 num4 num5

4.7479438e+01 . 47.4794 47.4794 47.4794 47.4794
3.4455325e+01 . 34.4553 34.4553 34.4553 34.4553
2.5549948e+01 . 25.5499 25.5499 25.5499 25.5499
2.0816337e+01 . 20.8163 20.8163 20.8163 20.8163
2.2582412e+01 . 22.5824 22.5824 22.5824 22.5824

Re: convert character to numeric

Ksharp — Thu, 01 Jun 2017 13:00:24 GMT

Try informat Ew.d

data have;
input x e32.;
format x 32.10;
cards;
4.7479438e+01
3.4455325e+01
2.5549948e+01
2.0816337e+01
2.2582412e+01
;
run;

Re: convert character to numeric

echoli — Thu, 01 Jun 2017 14:12:18 GMT

Yes, that's the point!!! it works! thanks so much!