- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
It should be read by default. What's the variable type/format? When you use INPUT() try using best32. Informat.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, that's the point!!! it works! thanks so much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;