The SAS Output Delivery System and reporting techniques

ODS TAGSETS.EXCELXP output multi-sheet

Reply
Contributor
Posts: 67

ODS TAGSETS.EXCELXP output multi-sheet

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

Occasional Contributor
Posts: 14

ODS TAGSETS.EXCELXP output multi-sheet

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

Contributor
Posts: 67

ODS TAGSETS.EXCELXP output multi-sheet

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

Occasional Contributor
Posts: 17

ODS TAGSETS.EXCELXP output multi-sheet

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.

SAS Super FREQ
Posts: 8,743

ODS TAGSETS.EXCELXP output multi-sheet

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

Contributor
Posts: 67

ODS TAGSETS.EXCELXP output multi-sheet

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

SAS Super FREQ
Posts: 8,743

ODS TAGSETS.EXCELXP output multi-sheet

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

Ask a Question
Discussion stats
  • 6 replies
  • 1098 views
  • 0 likes
  • 4 in conversation