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,
C
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
It should be read by default. What's the variable type/format? When you use INPUT() try using best32. Informat.
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.
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
Yes, that's the point!!! it works! thanks so much!
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;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.