topic Re: My export to excel is not retaining the designated column formats in SAS Enterprise Guide

My export to excel is not retaining the designated column formats

dwah — Thu, 30 May 2019 15:58:20 GMT

Hi,

I've created an excel sheet that contains numeric values and monetary values.

Initially, whenever I export the data to a .xlsx file. The formats that I set in SAS are never retained in the excel spreadsheet. For example,

numeric values with leading 0's would get removed if I were to double click the column. The same for supposed Currency columns, the $ sign would stay, however, I can't aggregate the cells because they're neither Numeric nor Currency columns. So pretty much, whenever, I run an excel export. Every column is "General" in terms of formatting.

My approach to this was to concatenate an apostrophe to every field that was numeric or monetary.

For example: '0001111

This avoids the 0's from being removed when I double click it. However, every time an export is ran. The apostrophes are visible and the formats are still considered "General." The upside to this is that when I double click the fields, nothing is removed, but the apostrophes, which is halfway to the desired outcome.

So, I'm wondering right now, what can I do to export an excel sheet that only shows apostrophes when looking at a cell from the Formula box. I don't want to see any apostrophes on the bare spreadsheet until I click a cell and view its Formula Box.

Or I can skip the concatenating of apostrophes and have the ability to retain formats when exporting to Excel.

Re: My export to excel is not retaining the designated column formats

Reeza — Thu, 30 May 2019 16:15:42 GMT

@dwah wrote:

Hi,

I've created an excel sheet that contains numeric values and monetary values.

Initially, whenever I export the data to a .xlsx file. The formats that I set in SAS are never retained in the excel spreadsheet. For example,

numeric values with leading 0's would get removed if I were to double click the column. The same for supposed Currency columns, the $ sign would stay, however, I can't aggregate the cells because they're neither Numeric nor Currency columns. So pretty much, whenever, I run an excel export. Every column is "General" in terms of formatting.

My approach to this was to concatenate an apostrophe to every field that was numeric or monetary.

For example: '0001111

This avoids the 0's from being removed when I double click it. However, every time an export is ran. The apostrophes are visible and the formats are still considered "General." The upside to this is that when I double click the fields, nothing is removed, but the apostrophes, which is halfway to the desired outcome.

So, I'm wondering right now, what can I do to export an excel sheet that only shows apostrophes when looking at a cell from the Formula box. I don't want to see any apostrophes on the bare spreadsheet until I click a cell and view its Formula Box.

Or I can skip the concatenating of apostrophes and have the ability to retain formats when exporting to Excel.

If you're using PROC EXPORT this is pretty standard behaviour.

However, ODS EXCEL provides a lot of functionality to format your output exactly to your specifications. It's not perfect but it's pretty good. You sometimes need to customize things to get the right formats but it's possible, whereas in PROC EXPORT it's not really.

This should get you started

https://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

Re: My export to excel is not retaining the designated column formats

Tom — Thu, 30 May 2019 16:16:56 GMT

Please provide example data and show what method you are using to "export" to XLSX.

Are you use PROC EXPORT? Using the XLSX libname engine? Using EXCEL engine? Or PCFILES?

Are you use ODS to store printed output into XLSX file?

Are you creating a CSV (or other delimited text file) and reading it use Excel? If do HOW are you reading it with Excel?

Re: My export to excel is not retaining the designated column formats

dwah — Thu, 30 May 2019 16:23:48 GMT

I am currently using PROC EXPORT. From what I've read so far on these sorts of issues, PROC EXPORT does indeed cause these sort of issues. I've taken a look into ODS EXCEL but I ran into memory issues when I last tried. Not sure if that's still a viable solution.

Thanks!

Re: My export to excel is not retaining the designated column formats

dwah — Thu, 30 May 2019 16:24:32 GMT

PROC EXPORT is the current way its being read to Excel.

Ods excel was causing memory issues when I attempted to use it as an export method.

Thanks!

Re: My export to excel is not retaining the designated column formats

Reeza — Thu, 30 May 2019 16:29:03 GMT

ODS EXCEL was only in production as of 9.4M3, but it seems fairly decent in 9.4M5/6. I have seen some memory issues when there are a large number of rows but not beyond that.

Other approaches could be to use TAGSETS.EXCELXP and then convert to XLSX afterwards but you may also run into memory issues.

Excel auto formats things which makes it hard sometimes.