Import Numeric variables with the format best.

[Dingdang]

Dear all

I have a problem importing numeric variable with different lengthes and decimal places with the format best. The variable X was not identified as numeric, so I used put(X, best.) to convert it into a numeric variable. Since the original data in excel have different lengthes and decimal places, some numbers that I read into SAS were wrong (like originally 0.00225 was imported as 2.25 etc.). How I can fix this problem?

Many thanks in advance! 

Best regards

Xiao

[Reeza]

Show your code and an example that replicates your issue please. 

Note that if you use an informat like 8.2 it assumes your data ALWAYS has two decimal points. So 822 becomes 8.22

And there's no real way to fix that after the fact, you need to fix the import step. 

[ballardw]

---

@Dingdang wrote:

Dear all

I have a problem importing numeric variable with different lengthes and decimal places with the format best. The variable X was not identified as numeric, so I used put(X, best.) to convert it into a numeric variable. Since the original data in excel have different lengthes and decimal places, some numbers that I read into SAS were wrong (like originally 0.00225 was imported as 2.25 etc.). How I can fix this problem?

Many thanks in advance! 

Best regards

Xiao

---

What format was the original file? How did you import it? If you used Proc Import and the file was CSV or other delimited file type then likely the GUESSINGROWS value was set too low. You can modify the code generated to read delimited files by copy and paste from the log to editor and change the informat for any affected variables. If you wrote a data step to read the data then show the code.

[Tom · Accepted solution]

You need to show the values you have and what values you want them translated into.  Also show the code you used and the values you got and how they are not what you want.

The way to convert a string to a number is to use an INFORMAT, not a FORMAT.  Note that there is no informat named BEST.  If you ask for it SAS will just silently use the normal informat it uses for reading numbers.  So input(x,BEST10.) is the same as input(x,F10.) which is the same as input(x,10.).  If your character strings could contain commas and/or dollar signs then the "best" informat to use would be the COMMA. informat.  Do not specify decimal places on an informat (unless you want SAS to divide strings that look like integers by a power of ten).

numvalue = input(strvalue, comma32.) ;