DATA Step, Macro, Functions and more

Reading decimals from xlsx/xls

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Reading decimals from xlsx/xls

[ Edited ]

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


Accepted Solutions
Solution
‎12-01-2016 12:07 AM
Super User
Super User
Posts: 7,942

Re: Reading decimals from xlsx/xls

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.

View solution in original post


All Replies
Super User
Posts: 7,762

Re: Reading decimals from xlsx/xls

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Reading decimals from xlsx/xls

Posted in reply to KurtBremser

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?

Super User
Posts: 7,762

Re: Reading decimals from xlsx/xls

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Reading decimals from xlsx/xls

Posted in reply to KurtBremser

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?

Super User
Posts: 7,762

Re: Reading decimals from xlsx/xls


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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,942

Re: Reading decimals from xlsx/xls

[ Edited ]

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-f...

 

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.

Occasional Contributor
Posts: 6

Re: Reading decimals from xlsx/xls

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

 

Attachment
Solution
‎12-01-2016 12:07 AM
Super User
Super User
Posts: 7,942

Re: Reading decimals from xlsx/xls

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.

Occasional Contributor
Posts: 6

Re: Reading decimals from xlsx/xls

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

Super User
Posts: 7,762

Re: Reading decimals from xlsx/xls

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 505

Re: Reading decimals from xlsx/xls

Posted in reply to KurtBremser
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;
☑ This topic is solved.

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

Discussion stats
  • 11 replies
  • 929 views
  • 0 likes
  • 4 in conversation