01-12-2012 11:11 PM
I am having a difficulty to get multi-sheet output into Excel. Could you please help me to check if there are anything wrong with the following SAS codes.
FILENAME WONWON 'ABC.EFG.EMAIL' DISP=(MOD,DELETE,DELETE) ;
FILENAME WONWON CLEAR ;
FILENAME WONWON 'ABC.EFG.EMAIL' DISP=(NEW,CATLG,DELETE) RECFM=VB
UNIT=3390 SPACE=(CYL,(500,1000)) ;
DEFINE STYLE STYLES.XLSANSPRINTER;
PARENT = STYLES.SANSPRINTER;
STYLE HEADER FROM HEADER/
FONT_FACE="TIMES NEW ROMAN"
FONT_FACE="TIMES NEW ROMAN"
ODS TAGSETS.EXCELXP FILE=WONWON STYLE = XLSANSRINTER RS = NONE;
ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="FILE1");
PROC PRINT DATA = FILENAME1;
ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="FILE2");
PROC PRINT DATA = FILENAME2;
ODS TAGSETS.EXCELXP CLOSE;
FILENAME SEND EMAIL
FROM = 'EMAILADDRESS'
TO = 'EMAILADDRESS'
SUBJECT = 'MULTI-SHEET'
ATTACH = ( 'ABC.EFG.EMAIL' EXT='XLS' NAME=' '
FILE SEND ;
PUT 'PLEASE RECEIVE THE ATTACHED. -- REGARDS,';
Thank you all, Any supplementary information is highly appreciated.
01-14-2012 09:37 PM
When I tried to open Excel file, it shows "Microsoft Excel Viewer cannot open files of this type."
I also added the code that you suggested, but it did not work either.
01-13-2012 09:21 AM
What is the setting of the sheet_interval option? For exanple,
ODS TAGSETS.EXCELXP options(sheet_interval='none' sheet_name = "Title" );
Try working with this option.
01-13-2012 12:37 PM
I see some other issues, which may have an impact. First, your content-type header is incorrect. TAGSETS.EXCELXP creates XML that conformis to Office 2003 Spreadsheet Markup language specification. So you are not actually sending the right content-type. XML is NOT HTML....I don't know whether that will make a difference to your mail server, but some mail servers have been configured NOT to send HTML or XML files. So that's something you'll need to check with your email folks.
Next....what errors are you getting??? Are they SAS errors or is the file being sent at all? Is the problem with Excel? If so what error message are you getting?
You might also want to work with Tech Support because if you are creating this file on the mainframe and sending it to a different system, then EBCDIC to ASCII conversion issues might also be at play.
01-14-2012 09:41 PM
I ran it on the Mainframe, and did not receive any errors. Also I received an email, but the problem is I could not open the Excel sheet which showed "Microsoft Excel Viewer cannot open files of this type."
01-15-2012 02:13 PM
SAS doesn't care what content-type header you send. But, the receiving application might care what content-type header it receives. And, you told Excel and the receiving operating system that the content-type was "text/html" -- however, that is incorrect. TAGSETS.EXCELXP is NOT creating an HTML file -- so your email is NOT HTML...but the receiving application is being told to expect HTML by your content-type header.
So I think that Excel is sending you a message that says you told it that the file was HTML, but the viewer didn't find HTML in the file. Perhaps if you sent a the correct content-type header, the Excel viewer might not give you an issue. Possible content type headers that would be more accurate are:
But the other thing is that you have to be sure that the "Microsoft Excel Viewer" can open and render Office 2003 Spreadsheet Markup Language XML -- and you might need to consult some Microsoft documentation that explains what the right content-type header is.
TAGSETS.EXCELXP creates Microsoft Office compliant Spreadsheet Markup Language XML, as specified for Excel 2003. So you have to be sure that you have a version of Excel or a viewer that can open that flavor of Office XML.
The other thing is that regular Excel, ever since Office 2007 complains when you send an XML or HTML file and give it a different file extension (such as .XLS)...you can frequently get this warning from Excel:
The file that you are trying to open "file" is in a different format than specified by the file extension.
Verify that the file is not corrupted and is from a trusted source before opening the file.
Do you want to open the file now?
as described in this Tech Support note:
It may be possible the Excel Viewer is giving you the equivalent of this message. In which case, you might change the content-type header as well as change the extension to XML instead of XLS for your email attachment. I still think your best bet for help is to work with Tech Support on this.