topic Re: Reading decimals from xlsx/xls in SAS Programming

Reading decimals from xlsx/xls

bhos123 — Wed, 30 Nov 2016 06:41:37 GMT

I have a xlsx file with variables having decimals upto 15. when I open the xls file only 4-7 decimals are visible(actual data in formula bar has 15 decimals but only 4 decimals are visible on the cell)

Now when I import the data in SAS only those visisble decimals are populating is SAS(only 4-7 decimals not 15), even when I use informats like 20.15 or best20.15 and formats like $100

Note: The excel file is not allowed to change

Re: Reading decimals from xlsx/xls

Kurt_Bremser — Wed, 30 Nov 2016 08:10:51 GMT

Export the data from Excel to a csv file and inspect that with a text editor, to see what Excel actually exports.

Note: The Excel file format is not suited for data transfer.

Re: Reading decimals from xlsx/xls

bhos123 — Wed, 30 Nov 2016 09:04:51 GMT

Thanks Kurt, The csv file itself showing 4 decimals, that means when I convert excel to csv, only the visible decimals are coming in csv(not all the 15 decimals)

How do I solve this problem without changing the excel file?

Re: Reading decimals from xlsx/xls

Kurt_Bremser — Wed, 30 Nov 2016 09:12:56 GMT

You will have to change the format on the columns and test if that makes a difference in the file(s). Save the Excel file to a new name to preserve the original one.

Be also aware that you might lose some precision on the import, as SAS can only store around 15 decimal digits in the mantissa of its 8-byte real format.

Re: Reading decimals from xlsx/xls

bhos123 — Wed, 30 Nov 2016 09:18:05 GMT

I tried this and it worked perfectly. The only problem is I am not allowed to increase the number of decimals in excel. That means I am not allowed to do any changes to excel file.

Is there any way I can achieve this without making any changes to my excel file?

Re: Reading decimals from xlsx/xls

RW9 — Wed, 30 Nov 2016 09:29:04 GMT

I have found your problem:

"Note: The excel file is not allowed to change"

Its the part wher you say something is not able to change, even if it may be wrong. Excel is a really poor data format for any purpose, you will come across many examples of this, hiding things, displaying data incorrectly and other features. First you will need to fix the export of that data into a file which contains all the data. If SaveAs-> CSV produces a file with only the displayed value of four digits (which is unusual), then you might need to export it yourself from VBA. Open VBA editor (alt+f11 usually), and write some code which loops over each cell and prints out to a plain file the correct value - this may be the value property or it might be the formula property or one of the others. Here shows how to print to file, modify it to use formatted values, or .format value depedning on what you need out.

http://stackoverflow.com/questions/15125578/vba-print-1-select-range-for-exporting-from-excel-to-a-file

Note that you don't need to save the file to do this, nor does VBA have to reside in the same file as the data. However fixing something that is broken should be your number 1 priority.

Re: Reading decimals from xlsx/xls

Kurt_Bremser — Wed, 30 Nov 2016 09:40:46 GMT

@bhos123 wrote:

I tried this and it worked perfectly. The only problem is I am not allowed to increase the number of decimals in excel. That means I am not allowed to do any changes to excel file.

Is there any way I can achieve this without making any changes to my excel file?

Well, you can make the changes in the spreadsheet, export to csv, and then not save back to the original format.

You WILL have to change the display formats before exporting, as SAS needs the formatted values to determine column attributes. Period.

This is all because Excel (and other spreadsheet programs) does not have the structure of a database, where attributes are fixed for a column throughout, and values and formats are two different and separate things. In Excel, each cell can have its own attributes. Since there is no column attribute (like "this is a date column") that would enable the separate transfer of raw values, you have to make the format changes.

Re: Reading decimals from xlsx/xls

bhos123 — Wed, 30 Nov 2016 09:59:57 GMT

Not working, I have attached the files.

Book1.xlsx - original file

Book1.csv - csv converted file(open with notepad or Import in SAS you will see only few decimals)

Book2.csv - csv converted using VBA code given below( same problem as above)

Sub vba_code_to_convert_excel_to_csv()
Set wb = Workbooks.Open("C:\Users\1307608\Desktop\Book1.xlsx")
wb.SaveAs Filename:="C:\Users\1307608\Desktop\Book2.csv", FileFormat:=xlCSV, CreateBackup:=False
End Sub

Re: Reading decimals from xlsx/xls

RW9 — Wed, 30 Nov 2016 10:21:42 GMT

Sorry, please re-read my post and look at the link provided. Step 1 and 2 you have done is exactly the same thing, you have just called the SaveAS CSV command form VBA which is not what I posted. The SaveAS CSV function streams the "values" as they are displayed to the CSV file which is where your problem lies. What you need is the VBA code in the link I sent you, but instead of outputting c.Value, you would need to output the particular proprty of each cell which contains the full value - it may be c.Formula. I can't tell offhand which property it is (and I am afraid I wouldn't download any Office files form the net), so just fiddle around with the different properties.

Re: Reading decimals from xlsx/xls

Kurt_Bremser — Wed, 30 Nov 2016 10:25:13 GMT

Just saving as csv from VBA will have the same effect as if you had done it manually, namely none.

Before saving as csv, you need to change the cell formats.

Re: Reading decimals from xlsx/xls

rogerjdeangelis — Wed, 30 Nov 2016 14:01:11 GMT

This shoud work regardless of the format(appearance) of the number in excel.

You will need to change to numeric in SAS, which you can do in the outer SQL using
input(ChrNum,best15.) as ChrNum

/* T0100580 Casting 15 digit excel numbers to character using passthru
Casting 15 digit excel numbers to character using passthru

inspired by
https://goo.gl/jnnQqa
https://communities.sas.com/t5/Base-SAS-Programming/Excel-import-Exponential-value-into-SAS-as-character-field/m-p/312116

HAVE ( Where X is numeric)

  +------------------+
  |        A         |
--+------------------+
1 | X                |
--|------------------+
2 |  1202220022121120|
---------------------+
3 |  1202220022121120|
---------------------+
4 |  1202220022121120|
--+------------------+

num18

WANT
====

WANT  (note the sheet name is num18 could be the default)
====

Up to 40 obs WORK.XLS_CAST total obs=3

Obs         CHRNUM

 1     1202220022121120
 2     1202220022121120
 3     1202220022121120

WORKING CODE

 format(X,'################') as ChrNum


FULL SOLUTION
=============

* create a sheet with the 15 digit numbers;

%utlfkil(d:/xls/utl_excel_cast.xlsx);
libname xel "d:/xls/utl_excel_cast.xlsx";
data xel.num18;
  do x=1202220022121121,1202220022121121,1202220022121121;
     output;
  end;
run;quit;
libname xel clear;

* cast the numbers to char using passthru;
proc sql dquote=ansi;
  connect to excel (Path="d:\xls\utl_excel_cast.xlsx" mixed=yes);
    create table xls_cast as
    select
        ChrNum     length=16
        from connection to Excel
        (
         Select
            format(X,'################') as ChrNum
         from
           num18
        );
    disconnect from Excel;
Quit;

Re: Reading decimals from xlsx/xls

bhos123 — Thu, 01 Dec 2016 05:08:45 GMT

Thanks RW9. This c.value worked for me perfectly.