SAS reading 0.07 string as scientific - still

[abigail_greenh]

I found an identical topic posted in 2017 and have the same issue, which was not solved despite the lock. The listed solution is not correct.

I imported a huge dataset from Excel and converted a few selected variables to numerical. On conversion, SAS reads only the value 0.07 incorrectly, as 7.0000000000000007E-2. It does this in every instance and every other value is correct. The cell(s) do not have any other hidden values, and contain only the characters "0.07" even when expanded, so it's not a case of hidden extra decimals. I tried copy-pasting a value from a different cell and changing it to 0.07. To convert the string to a numerical value, I tried best12, bestw, and bestd.w.

proc import out=site
datafile="file.xlsx"
dbms=xlsx replace;
sheet="sheet";
getnames=yes;

data site_num;
set site;
num_PW=input(PW, BESTw.);
run;

[Patrick]

Can you share a cut-down version of your Excel that shows this behaviour? Ideally an Excel with only one sheet and one row of data and the SAS code to read it that then returns the undesired result.

[Tom]

You might want to open a ticket with SAS support so that you can share your actual file with them and they can see if the behavior is the same on other versions of SAS running on different hardware.

Note that if you are importing from an XLSX file then SAS is not reading a STRING .  It is converting the floating point number you have stored in the XLSX file into the floating point number you have in the SAS dataset.  If it was reading it as a string it would have made a character variable in SAS.  So the issue might be on the EXCEL side.  

[FreelanceReinh]

Hello @abigail_greenh,

To convert the 21-character string "7.0000000000000007E-2" to a numeric value, use an informat with a length >=21, for example the 32. informat:

num_PW=input(PW, 32.);

The informat BEST12. (which is just an alias of 12.) has only length 12 and hence would miss the important "E-2" information, resulting in the integer value 7. Also note that the "w " (written in italics) in format and informat names found in the SAS documentation must always be replaced by a numeric width specification (and "d " by a number of decimals, but these are not needed in your example).

I can confirm that 0.07 is stored as 7.0000000000000007E-2 in an .xlsx file created with Excel 2013 on Windows 10. After unzipping the underlying xml files of an Excel sheet containing the numbers 0.01, 0.02, ..., 0.11 I see that, among these 11 numbers, only 0.07 has that precision issue. Here is an excerpt of sheet1.xml:

...
<row r="6" spans="1:1" x14ac:dyDescent="0.25"><c r="A6"><v>0.06</v></c></row>
<row r="7" spans="1:1" x14ac:dyDescent="0.25"><c r="A7"><v>7.0000000000000007E-2</v></c></row>
<row r="8" spans="1:1" x14ac:dyDescent="0.25"><c r="A8"><v>0.08</v></c></row>
...

I found several discussions of this Excel problem on the web from 2009 and later years.

Addendum: By the way, the internal binary representation of 0.07 in SAS on Windows and Unix platforms, mathematically translated back to the decimal system, equals

0.070000000000000006661338147750939242541790008544921875

Rounded to 18 decimals, this matches the value 7.0000000000000007E-2 seen in Excel.

Addendum 2: SAS (at least my Windows SAS 9.4M5) would interpret the numeric literal 7.0000000000000007E-2 as the same value as 0.07,

45    data _null_;
46    if 7.0000000000000007E-2=0.07 then put 'OK';
47    run;

OK

although the internal representation corresponding to that decimal fraction, derived mathematically, would have a numeric value slightly greater than 0.07 (see this 2024 post for more details).

[abigail_greenh]

@FreelanceReinh Thank you, that makes sense and is a fun little piece of Excel trivia to share with my colleagues. I'm required to use these file formats in my workplace, so do you know if there's anything I can do on the SAS or Excel side to display the correct decimal number? 

[FreelanceReinh]

Are there cases in your data where num_PW=input(PW, 32.) does not contain the "correct decimal number", that is, where a check like

371   data _null_;
372   PW="7.0000000000000007E-2";
373   num_PW=input(PW, 32.);
374   if num_PW=0.07 then put 'OK';
375   run;

OK

would not yield "OK"? If so, I would use the ROUND function with an appropriate rounding unit in the definition of num_PW.

Example:

412   data _null_;
413   PW="7.0000000000000010E-2";
414   num_PW=round(input(PW, 32.), 1e-14);
415   if num_PW=0.07 then put 'OK';
416   run;

OK