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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.