07-11-2013 12:04 PM
I run a Proc Export procedure to create an Excel (.xls) file from a SAS dataset, but I keep losing the comma formatting in the resultant Excel file from numbers that I knew had it in the SAS dataset used as the input. I currently use PC SAS 9.2, 32-bit version. If helpful, the code I used is below:
proc export data = nsc.msa_ftp_final_comma
outfile = "c:\test\msa_ftp_final_comma.xls"
I have to restore the lost comma formattting manually by opening up each Excel file and doing "Format Cells" on the applicable columns. I also tried using the dbms=xls option but it creates a messed-up Excel file.
Does anyone have any suggestions?
07-11-2013 12:24 PM
Check your log, it states that formats are not outputted to excel using proc export.
Search on here to find the solution as this question has been asked/answered many times.
KEYWORDS: Formats Excel Export
07-11-2013 03:04 PM
Thanks. I'll search and try to find a solution.
I did check my SAS Log but it mentions nothing about "formats are not outputted to excel using proc export." It just says that the "range/sheet was successfully created."
07-11-2013 04:23 PM
That's the note I usually get, but I export using a libname and are you're using proc export, through the point and click or code?
NOTE: SAS variable labels, formats, and lengths are not written to DBMS
07-11-2013 05:24 PM
Reeza's note is correct. I do not believe PROC EXPORT sends you the informative note; although the LIBNAME engine does.
But, usually, the SAS formats, labels, etc are not sent to Excel with "export" methods in general. So if you use certain SAS formats on your variables, such as Z (for leading zeroes) or other formats, Excel doesn't get them, so it uses a default format when it loads the sheet.
This is one of the reasons why people use ODS methods to send output to files for Excel to open -- because ODS has a way to send Microsoft-specific formatting instructions to Excel. But depending on what you need to do, it may be faster to reformat the columns.