topic Re: Proc Export to Excel (.xls) -- losing comma formatting in SAS Procedures

Proc Export to Excel (.xls) -- losing comma formatting

WLP165 — Thu, 11 Jul 2013 16:04:01 GMT

Hello,

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"

dbms=excel replace;

run;

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?

William

Re: Proc Export to Excel (.xls) -- losing comma formatting

Reeza — Thu, 11 Jul 2013 16:24:02 GMT

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

Re: Proc Export to Excel (.xls) -- losing comma formatting

WLP165 — Thu, 11 Jul 2013 19:04:26 GMT

Rezza,

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

Re: Proc Export to Excel (.xls) -- losing comma formatting

Reeza — Thu, 11 Jul 2013 20:23:54 GMT

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

tables.

Re: Proc Export to Excel (.xls) -- losing comma formatting

WLP165 — Thu, 11 Jul 2013 20:49:20 GMT

I am using the proc export procedure in base SAS (code). I do not use SAS Enterprise Guide.

William

Re: Proc Export to Excel (.xls) -- losing comma formatting

Cynthia_sas — Thu, 11 Jul 2013 21:24:54 GMT

Hi,

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.

cynthia