DATA Step, Macro, Functions and more

convert character to numeric

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

convert character to numeric

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
Solution
‎06-01-2017 10:12 AM
Valued Guide
Posts: 765

Re: convert character to numeric

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

View solution in original post


All Replies
Super User
Posts: 17,905

Re: convert character to numeric

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

Super User
Posts: 5,092

Re: convert character to numeric

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.

Solution
‎06-01-2017 10:12 AM
Valued Guide
Posts: 765

Re: convert character to numeric

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

Frequent Contributor
Posts: 76

Re: convert character to numeric

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

Super User
Posts: 9,687

Re: convert character to numeric

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 165 views
  • 0 likes
  • 5 in conversation