topic ODS TAGSETS.EXCELXP output multi-sheet in ODS and Base Reporting

ODS TAGSETS.EXCELXP output multi-sheet

Belle — Fri, 13 Jan 2012 04:11:22 GMT

Dear Members,

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.

OPTION ORIENTATION=LANDSCAPE;

FILENAME WONWON 'ABC.EFG.EMAIL' DISP=(MOD,DELETE,DELETE) ;

FILENAME WONWON CLEAR ;

FILENAME WONWON 'ABC.EFG.EMAIL' DISP=(NEW,CATLG,DELETE) RECFM=VB

LRECL=20004 BLKSIZE=27998

UNIT=3390 SPACE=(CYL,(500,1000)) ;

PROC TEMPLATE;

DEFINE STYLE STYLES.XLSANSPRINTER;

PARENT = STYLES.SANSPRINTER;

STYLE HEADER FROM HEADER/

FONT_FACE="TIMES NEW ROMAN"

FONT_SIZE=12PT

BACKGROUND=YELLOW

JUST=CENTER

FONT_FACE="TIMES NEW ROMAN"

FONT_SIZE=11PT

BACKGROUND=CXB0B0B0;

END;

RUN; QUIT;

ODS TAGSETS.EXCELXP FILE=WONWON STYLE = XLSANSRINTER RS = NONE;

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="FILE1");

PROC PRINT DATA = FILENAME1;

RUN;

ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="FILE2");

PROC PRINT DATA = FILENAME2;

RUN;

ODS TAGSETS.EXCELXP CLOSE;

FILENAME SEND EMAIL

FROM = 'EMAILADDRESS'

TO = 'EMAILADDRESS'

SUBJECT = 'MULTI-SHEET'

ATTACH = ( 'ABC.EFG.EMAIL' EXT='XLS' NAME=' '

CONTENT_TYPE='TEXT/HTML')

;

DATA _NULL_;

FILE SEND ;

PUT 'PLEASE RECEIVE THE ATTACHED. -- REGARDS,';

RUN ;

Thank you all, Any supplementary information is highly appreciated.

Belle

ODS TAGSETS.EXCELXP output multi-sheet

Jay_OAG — Fri, 13 Jan 2012 13:05:53 GMT

What errors are you getting? Are you able to generate any excel documents?

ODS TAGSETS.EXCELXP output multi-sheet

LSchafer — Fri, 13 Jan 2012 14:21:00 GMT

Hi,

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.

Good Luck.

ODS TAGSETS.EXCELXP output multi-sheet

Cynthia_sas — Fri, 13 Jan 2012 17:37:01 GMT

Hi:

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.

cynthia

ODS TAGSETS.EXCELXP output multi-sheet

Belle — Sun, 15 Jan 2012 02:37:55 GMT

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.

Thanks

ODS TAGSETS.EXCELXP output multi-sheet

Belle — Sun, 15 Jan 2012 02:41:46 GMT

Hi Cynthia,

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

Thanks

ODS TAGSETS.EXCELXP output multi-sheet

Cynthia_sas — Sun, 15 Jan 2012 19:13:55 GMT

Hi:

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:

text/xml OR

application/vnd.ms-excel

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:

http://support.sas.com/kb/31/956.html

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.

cynthia