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

[WLP165]

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

[Reeza]

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

[WLP165]

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

[Reeza]

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.

[WLP165]

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

William

[Cynthia_sas]

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