Help using Base SAS procedures

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

Reply
Contributor
Posts: 23

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


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

Super User
Posts: 17,899

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

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

Contributor
Posts: 23

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

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

Super User
Posts: 17,899

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

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.

Contributor
Posts: 23

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

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

William

SAS Super FREQ
Posts: 8,743

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

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

Ask a Question
Discussion stats
  • 5 replies
  • 613 views
  • 1 like
  • 3 in conversation