topic Re: Import value with 10 decimal places in SAS Programming

Import value with 10 decimal places

David_Billa — Wed, 19 Jan 2022 08:15:45 GMT

I'm trying to Import value from Excel (.xls) to create the SAS dataset. I see that after Import, value is getting rounded which I dont want.

Value in Excel is 0,001620588 and after Import I need the same value 0.001620588 in SAS dataset.

I tried the following step in data step but the value is rounded to 0.00162059

(input(A,Best32.)) as Value length = 8.

Even I tried with the Format 15.12 but still it's not producing the desired result. Any help?

Re: Import value with 10 decimal places

Kurt_Bremser — Wed, 19 Jan 2022 08:36:30 GMT

Your code is NOT valid data step code, it looks like SQL.

You must first and foremost question yourself why you have a character variable after import when it should be numeric. This is most likely a result of the guessing of PROC IMPORT, caused by the use of the <add a list of expletives here> Excel file format for data transfer.

Once again, my advice is to save the spreadsheet to a csv file and read that with a data step you write yourself, taking care of proper variable typing, and where you can use a custom informat to deal with any special values that caused IMPORT to read the variable as character.

Re: Import value with 10 decimal places

David_Billa — Wed, 19 Jan 2022 08:53:12 GMT

Let me simplify my question. Which format to use in INPUT function to
generate the value with 10 digits after decimal point?

Re: Import value with 10 decimal places

Kurt_Bremser — Wed, 19 Jan 2022 12:23:18 GMT

As long as your string has all decimals, the number will also have it, until you run into the limits of numeric precision:

data _null_;
string = "0.001620588";
num = input(string,best32.);
format num 23.20;
put num=;
run;

Log:

 69         data _null_;
 70         string = "0.001620588";
 71         num = input(string,best32.);
 72         format num 23.20;
 73         put num=;
 74         run;
 
 num=0.00162058800000000000

Numeric precision comes into play in this example:

 69         data _null_;
 70         string = "0.12345678901234567890";
 71         num = input(string,best32.);
 72         format num 23.20;
 73         put num=;
 74         run;
 
 num=0.12345678901234000000

So you must inspect your character values created by PROC IMPORT.

For further help, post example data as a data step with datalines, and/or attach the Excel file so we can show you the proper way to import the data through a csv file.

Re: Import value with 10 decimal places

ballardw — Wed, 19 Jan 2022 15:59:33 GMT

@David_Billa wrote:
Let me simplify my question. Which format to use in INPUT function to
generate the value with 10 digits after decimal point?

Basically "10 digits after decimal" has little to do with the INPUT and more to do with the assigned format.

You have to display at least 12 characters, the leading 0, the decimal and then the desired digits.

data example;
   x=0.12345678910234;
   put "Format: best5 " x= best5.;
   put "Format: best6 " x= best6.;
   put "Format: best7 " x= best7.;
   put "Format: best8 " x= best8.;
   put "Format: best9 " x= best9.;
   put "Format: best10" x= best10.;
   put "Format: best11" x= best11.;
   put "Format: best12" x= best12.;
   put "Format: best13" x= best13.;
   put "Format: f10.  " x= f10.;
   put "Format: f12.10" x= f12.10;
run;

Re: Import value with 10 decimal places

Tom — Wed, 19 Jan 2022 16:15:47 GMT

Does the value in EXCEL actually have a comma as the decimal point instead of period? Or was that a typo in your question?

SAS stores numbers as floating point values, so within the limits of the precision of storing decimal fractions using binary floating point values the numbers should match.

If you want to change the way the floating number is displayed then adjust the FORMAT you use to DISPLAY the number as a text string. If you don't attach a format then most places SAS will use BEST12. format to display the value. Although I believe that PROC SQL select will use BEST8. instead.

361   data _null_;
362     x=0.001620588;
363     put x= best12. / x=best8. / x=12.10 ;
364   run;

x=0.001620588
x=0.001621
x=0.0016205880

Re: Import value with 10 decimal places

David_Billa — Wed, 19 Jan 2022 16:18:12 GMT

Excel have a COMMA as a decimal point.

Re: Import value with 10 decimal places

Tom — Wed, 19 Jan 2022 16:38:44 GMT

So if the column has numbers in it Excel why are you using INPUT()? INPUT() requires a character string as input, not a number.

Does the column in Excel have mixed cell types? That would cause SAS to create the variable as character instead of numeric. If you remove the cells that have character strings from the column in the Excel file then SAS will convert the column into a numeric variable.