ODS to xls works but not to xlsx....... help..

[deleted_user]

This works fine........... I get my spreadsheet exactly how I need it, for Excel 2003.

ODS CHTML FILE='C:\ACE_test.xls' HEADTEXT="";
TITLE;
PROC PRINT DATA=EVERYTHING NOOBS;
RUN;
ODS HTML CLOSE;

Now I have to output to Excel 2007. When I change .xls to .xlsx I cannot open the file in Excel. I have more than 65K rows that is why we have Excel 2007, now I need to figure out how to make the ODS work.

Can you help me? Thanks.

[sbb]

I expect that this SAS support website http://support.sas.com/ note is applicable, even though ODS is not mentioned. You may want to investigate your options using ODS CSV, possibly, or open a SAS support tracking issue for more current information on support status, likely not until SAS 9.2, if then (phase 1 release). Also, there are other technical papers and SAS technotes on the SAS support website - suggest you may check there for more information.

Scott Barry
SBBWorks, Inc.

Usage Note 19774: SAS® 9.1.3 SP4 for Windows does not support access to Microsoft Office 2007 Excel and Microsoft Access 2007 with cetain SAS/ACCESS® software

http://support.sas.com/kb/19/774.html

Usage Note 31956: Using the MSOffice2k ODS destination with Microsoft Excel 2007 generates a dialog box
http://support.sas.com/kb/31/956.html

[Cynthia_sas]

Hi:
Scott is correct. The last URL that he shows is the relevant Tech Support Note. The SAS/Access note is not relevant, directly, to output created with ODS.

First, some clarification (this is my standard disclaimer). When you use ODS to create files, you are NOT, NOT, NOT creating true, binary Excel files. You are merely creating ASCII text files in various formats -- and Excel knows how to open and render these ASCII text files:

1) CSV-based: ODS CSV, ODS CSVALL create CSV (comma-separated value files) that Excel knows how to open and render.
2) HTML-based: ODS HTML, ODS MSOFFICE2K, ODS CHTML, ODS PHTML, etc all create HTML files that Excel knows how to open and render.
3) XML-based: ODS TAGSETS.EXCELXP creates Spreadsheet Markup Language XML files that Excel knows how to open and render.

You can verify the ASCII file that you created by starting Notepad and then opening the ODS-created output files with Notepad. Once you open these files with Notepad, you will see that they are merely, CSV or HTML or XML files that you created.

The "new" Office Open format .XLSX is really an ARCHIVE file. It is not ONE file, it is a collection of files. You can prove this to yourself by taking SAS out of the equation:
1) open Excel 2007,
2) starting a new workbook. Type a few rows on one worksheet. Got to Sheet 2, type a few rows on sheet 2.
3) Then, save the file as an .XLSX file.
4) Now, go to Windows Explorer. Rename the .XLSX file to .ZIP.
5) Open the newly renamed file with WinZip and look inside. By default, the "native" Open Office XML format (.XLSX, .DOCX, .PPTX) are all archive files.

ODS does NOT create this archive/XLSX file format. It only creates Excel 2002/2003 type of XML.

Generally, when you use SAS, you can "fool" the Windows Registry into opening Excel by giving your CSV or HTML or XML file a file extension of ".xls". However, when I tried to use ODS CHTML and gave the HTML file an extension of .XLSX, I got this error message from Excel 2007:

Excel cannot open the file 'myfile.xlsx' because the
file format or file extension is not valid. Verify that the file has not
been corrupted and that the file extension matches the format of the file.

And the error message is telling the truth. The internal HTML form of the file does NOT match the XLSX file extension. The fact that Excel treats the files as corrupted is reasonable, but undesirable.

However, if I do this:
[pre]
data trybig;
do var1 = 1 to 70000;
var = 'XXX'||put(var1,5.);
var2 = var1 * 2;
output;
end;
run;

ods listing close;
title 'See All 70000?';
ods csv file='c:\temp\big_comma.csv';
ods chtml file='c:\temp\big_ht.xls' ;
ods msoffice2k file='c:\temp\big_mso.xls' style=minimal;
ods tagsets.excelxp file='c:\temp\big_xp.xls';

proc print data=trybig;
run;
ods _all_ close;
[/pre]

Any of the above files WILL open in Excel and I see all 70000 observations. However, I just "fooled" the Windows registry by using the .XLS extension. I could just as well have named the files with .HTML or .XML and then opened them with Excel.

However, because I have used the .xls extension, I see this message on everything but the CSV file:
The file you are trying to open 'big.xls' is in a different format than specified by the file extension. Verify the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?
Yes No Help

This is the message described in Scott's pointer URL:
http://support.sas.com/kb/31/956.html

According to Microsoft, they are only issuing the message to help you:
http://office.microsoft.com/en-us/excel/HA102464701033.aspx

I don't know why Microsoft is so snarky if you try to use .XLSX instead of .XLS. But, technically, they -are- correct. None of the files that ODS creates is an .XLSX file. The best you can do is name the files with the .XLS extension and instruct your users to open them by answering yes to the prompt from Office 2007. Or, change the Windows registry, as outlined in this Microsoft article:
http://support.microsoft.com/kb/948615

cynthia

[deleted_user]

thanks scott and cynthia....... i will use your suggestions. i appreciate your comments.
-kat