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.
@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/
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!
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?
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.