errors creating an excel spreadsheet

[deleted_user]

we are having a problem creating a spreadsheet to email at the end of the program

I'm using code shown below. The problem is SAS seems to be outputting html and not a spreadsheet. Is there a way to make SAS output a spreadsheet?

data _null_;
%let path = d:\sas_reports;
call symput('report_name', "'&path\report.xls'");
run;

ods listing;
ods select all;
title "Report";
ods html body=&report_name;
ods proclabel "report"; run;

proc report data=extract_stats split='*'
style(report)={font_size=9pt} style(column)={font=('times new roman',9pt)} ; column dbs fp rows; run;quit;

run;quit;
ods pdf close;
ods listing close;

[sbb]

Your code mentions "ODS PDF" but opens with "ODS HTML" -- also, it's unclear how you are sending the EMAIL since there is no code listed for that part of your SAS program. And your ODS HTML statement has an invalid syntax, where the BODY= keyword is not surrounded in quotes.

For clarification, when your XLS file is generated, and you double-click the file, do you see an Excel document or something else? Yes, the spreadsheet data is going to be in HTML format, which Excel can interpret.

Please help by explaining the symptom you are experiencing, more specifically to incude your SAS-generate log output where the program is executed (along with the SAS code expanded in the log), rather than attempting to copy/paste what code you have provided -- having the exact log information will help with others reviewing your situation to make a recommendation.

Scott Barry
SBBWorks, Inc.

Using ODS to Generate Excel Files
Chevell Parker
http://support.sas.com/rnd/base/ods/templateFAQ/Excel1.pdf

[Cynthia_sas]

Hi:
There are only 2 ways to create an Excel binary workbook with SAS: PROC EXPORT or the Excel LIBNAME engine.

When you use ODS to create markup language files, you are creating ASCII text files that Excel knows how to open and render. Even if you give your ODS file an extension of '.xls', if you look at the output file with Notepad, you will see that it is either a CSV file, an HTML file or a SpreadsheetML file.

Ever since Excel 97, Excel has been able to open and render HTML files, When ODS HTML first came out, many programmers used this "trick" to fool the Windows registry:
[pre]
ods html file="c:\temp\report.xls";
...instead of ...
ods html file="c:\temp\report.html";
[/pre]

That was so that when they double clicked on the file with the .xls extension, the Windows registry would automatically launch Excel and Excel would detect the HTML, but would open the file But, it was really a trick. The "internals" of the file were still HTML -- and, if you did a FILE --> SAVE AS, Excel would show you HTML as the "save as" file type -- so Excel knew that you were trying to fool it, but was showing you that it also knew what the original file type should have been.

There are 3 ways to create a file for Excel using ODS:
1) ODS CSV -- creates a comma separated file that you could open with Excel or Microsoft Access or FoxPro -- or any application that could read CSV files
2) ODS HTML -- creates an HTML file that you can open with Excel or Word -- or any application (including a browser) that can read HTML files
3) ODS TAGSETS.EXCELXP -- creates a Microsoft Spreadsheet Markup Language XML file that you can open with Excel 2002 or higher

In addition, there is a "flavor" of HTML destination, called TAGSETS.TABLEEDITOR, which also allows you to create an HTML file with some embedded JavaScript -- and the JavaScript can launch Excel and display the HTML information.

About your program.... you have ODS HTML at the top and ODS PDF CLOSE at the bottom...this will not work. You should have ODS HTML CLOSE at the bottom. You don't need a DATA _NULL_ to set the path or file name for the report -- you could have set the &REPORT_NAME macro variable in one %LET statement...not sure why you have a %LET inside a DATA step program with a CALL SYMPUT anyway -- sort of apples and oranges.

Also, Scott is correct....your invocation should be:
[pre]
ods html body="&reportname";
...more code...
ods html close;
[/pre]

If you are trying the automatic e-mail with Enterprise Guide, I suspect that even with an XLS file extension, that EG will set the content-type header for the mail as "text/html". If you are using program statements to perform your email, then we'd really need to see the rest of your code, as Scott suggested.

Also, you should be aware of the fact that many sites prohibit the automatic emailing of HTML files through their mail server and force all mail to be plain text. If this is the case with your mail server, you might consider emailing a CSV file, instead.

cynthia