BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bhos123
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

11 REPLIES 11
Kurt_Bremser
Super User

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.

bhos123
Calcite | Level 5

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?

Kurt_Bremser
Super User

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.

bhos123
Calcite | Level 5

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?

Kurt_Bremser
Super User

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

bhos123
Calcite | Level 5

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

bhos123
Calcite | Level 5
Thanks RW9. This c.value worked for me perfectly.

Kurt_Bremser
Super User

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.

rogerjdeangelis
Barite | Level 11
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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