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
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
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."
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.
I am using the proc export procedure in base SAS (code). I do not use SAS Enterprise Guide.
William
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
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!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.