The SAS Output Delivery System and reporting techniques

Missing tabs in Excel while using ExcelXp tagsets

Reply
Super Contributor
Posts: 275

Missing tabs in Excel while using ExcelXp tagsets

Hello,
I using ExcelXP tagsets to generate several tabs in Excel workbook. The first 2 tabs seem to display correctly. The 3rd tab does show up in the HTML output (after a page break) but it is missing in the XML file. Am I missing something?

I looked at some of the older postings and a couple of SUGI papers. It may have to do with setting the sheet_name and sheet_interval options. I played around with them but no success.

My code is here -

ods tagsets.ExcelXP file="sample_report_v7_sql.xls" path="&source." style=sasweb ;

*Create Title Page;
ods tagsets.ExcelXp options(sheet_name='Title Page'
sheet_interval='none');

*Create second tab;
ods tagsets.ExcelXp options(embedded_titles='yes' EMBED_TITLES_ONCE='yes'
CONVERT_PERCENTAGES='yes'
sheet_name='2.4.1.2 Business Unit HA'
sheet_interval='none'
/* absolute_column_width='9' */
orientation='landscape'
pagebreaks='yes'
TITLE_FOOTNOTE_WIDTH='5'
doc='help'
zoom='60'
ABSOLUTE_COLUMN_WIDTH='28,28,'
/* DEFAULT_COLUMN_WIDTH='10'*/
wraptext='yes'
autofit_height='yes'
ROW_HEIGHT_FUDGE='5');

several procedures...

*Create third tab;
ods tagsets.ExcelXp options(sheet_name='none');

ods tagsets.ExcelXp options(embedded_titles='yes' EMBED_TITLES_ONCE='yes'
CONVERT_PERCENTAGES='yes'
sheet_name='Consumer Business Unit HA'
sheet_interval='yes'
/* absolute_column_width='9' */
orientation='landscape'
pagebreaks='yes'
TITLE_FOOTNOTE_WIDTH='5'
doc='help'
zoom='60'
ABSOLUTE_COLUMN_WIDTH='28,28'
/* DEFAULT_COLUMN_WIDTH='10'*/
wraptext='yes'
autofit_height='yes'
ROW_HEIGHT_FUDGE='5');

Is there any mistake? I dont see the 3rd tab in the XML output.

Thanks,
Raj.
SAS Super FREQ
Posts: 8,866

Re: Missing tabs in Excel while using ExcelXp tagsets

Hi:
I don't understand what you mean when you say:
"The 3rd tab does show up in the HTML output "
.. because 1) I do not see any ODS HTML statements which would create HTML and 2) when my browser opens an ODS HTML file, I don't have any "tabs" based on the code -- all my ODS HTML output -- no matter for how many procedures -- is all in one HTML page.

Since you did not post ALL your code, I would recommend that you check the usual things:
1) do all your procedures have the appropriate RUN; or QUIT; for their step boundaries?
2) does the procedure for the 3rd tab actually create output? (For example TAGSETS.EXCELXP will not "accept" SAS/GRAPH procedure output) -- so are you sure that the output is OK for the Excel SpreadsheetML 2003 "flavor" of XML???
3) Since you did not post your code, I cannot see whether you have a final
ODS TAGSETS.EXCELXP CLOSE;
statement -- are you closing the destination appropriately?
4) If you simplify your code to the absolute basics (without sheet_name and sheet_interval and doc= and row_height, etc), do you finally get 3 tabs???
5) have you desk checked all the values for your suboptions. For example, I note that in one place, you have sheet_interval='yes' and if you look at the DOC='HELP' output, the valid values for sheet_interval are:
[pre]
Values: Table, Page, Bygroup, Proc, None.
[/pre]
I do not see "Yes" in the list of valid values for sheet_interval -- however, even if that is OK...you don't show the procedure code that is supposed to put output on the 3rd tab.

The much simplified code below created 4 tabs for me (because I had sheet_interval='page' -- in the last step).

cynthia
[pre]
ods tagsets.ExcelXP file="sample_report_v7_sql.xls" style=sasweb ;

*Create First/Report Fake Title Page;
ods tagsets.ExcelXp options(sheet_name='Title Page' sheet_interval='none');
proc report data=sashelp.class nowd noheader;
where name = 'Alfred';
column name age height weight;
run;

*Create second tab;
ods tagsets.ExcelXp options(embedded_titles='yes' EMBED_TITLES_ONCE='yes'
sheet_name='2.4.1.2 Business Unit HA'
sheet_interval='none');

proc report data=sashelp.class nowd;
title 'My Second Sheet';
where sex = 'F';
run;

proc report data=sashelp.class nowd;
where sex = 'M';
run;

*Create third and fourth tab;

ods tagsets.ExcelXp options(embedded_titles='yes' EMBED_TITLES_ONCE='yes'
sheet_name='Consumer Business Unit HA'
sheet_interval='page');

proc report data=sashelp.shoes nowd;
title 'Slippers';
where product = 'Slipper' and region in ('Pacific', 'Western Europe');
define region / group;
break after region /page;
run;

ods _all_ close;
[/pre]
Super Contributor
Posts: 275

Re: Missing tabs in Excel while using ExcelXp tagsets

Posted in reply to Cynthia_sas
Hi Cynthia,
Sorry for not being very clear in my post. I am running the code in EG. So, I have the HTML output enabled just for my viewing purpose. The main output is on a XML file which is on the Unix server.

Many thanks for your 5 points. I had the ODS TAGSETS.EXCELXP CLOSE right after the second tab which disabled the 3rd tab output.

A quick question - What is the difference between throwing the output to a XLS file vs a XML file while using ExcelXP tagsets. These reports need to be run as a stored process later on. Will the extension make any difference?
SAS Super FREQ
Posts: 8,866

Re: Missing tabs in Excel while using ExcelXp tagsets

Hi:
Ah, well, prematurely closing the destination would tend to cause the output to cease flowing to the closed destination!

In some earlier versions of SAS, if you named the file with .XML as the file extension, a browser or XML editor (like XMLSpy) opened the TAGSETS.EXCELXP file instead of having Excel launch. So, when I post TAGSETS.EXCELXP code to the forum, since I don't have any idea who is running those older/earlier versions of SAS, I tend to use '.XLS' as the file extension to avoid a followup question of "Why does the browser open my TAGSETS.EXCELXP file?". Excel is perfectly happy, usually, to open files with the .XML extension from the FILE --> Open menu inside Excel. Besides, merely NAMING the file with a .XLS extension does not make the file a "true, binary" Excel file. You could open my output file with Notepad (or VI on UNIX) and you would see XML markup tags inside the file, no matter what file extension you give the file.

The ONLY way to create "true, binary" Excel files is to use PROC EXPORT or the SAS Excel LIBNAME Engine (or the SAS/Access ODBC or OLE-DB products). ODS -- no matter which "flavor" of ODS you use -- only creates ASCII text files and Excel happens to be able to open and render those ASCII text files, no matter what file extension you give the files. You can prove this to yourself by using ODS HTML, ODS CSV or ODS TAGSETS.EXCELXP to create result files and name the files with the .XLS file extension, but then BEFORE you just double-click on the file that you've created, try opening the result file from any of those ODS "flavors" with a text editor, you will see the underlying "markup" language info in the file -- not binary Excel formatting.

However, you also asked something that needs a BUNCH of clarification. You asked:
"These reports need to be run as a stored process later on. Will the extension make any difference? "


Here are some facts about stored processes (long post warning):
1) generally with a stored process, you are creating results that come directly back to a client application (such as Word, Excel, EG, or a browser or Web Report Studio) -- there is technically no filename or file extension for the output results from a stored process -- so the issue/question of file extension for stored process output is mostly a non-issue.

2) If you use SAS Enterprise Guide to convert an EG task or project or a stored process -- EG inserts (or will insert) the appropriate %STPBEGIN/%STPEND; macro invocations -- there is no easy way to specify a FILE= option with these stored process macros that is universally accepted by all the client applications, and in fact, I can't think of any client applications that would even know what to do with a FILE= option that had a filename and a file extension.

3) The best way to use FILE= with a stored process is to use the reserved file reference (FILEREF) of _WEBOUT -- which is essentially a "pipeline" from a client application to a server (stored process server usually) -- and again, _WEBOUT does NOT have a filename or file extension.

4) You can tell a browser what "helper" application can be used to open a _WEBOUT stream (such as Adobe Acrobat for PDF _WEBOUT or Excel for TAGSETS.EXCELXP _WEBOUT) by using the STPSRV_HEADER function to change the HTTP content-type header that is sent for any non-HTML _WEBOUT stream of output.

5) BI Platform client applications all have their own "preferences" when it comes to receiving stored process results. For example -- Web Report Studio and Microsoft PowerPoint will ONLY accept SASReport XML results from a stored process. So if you use TAGSETS.EXCELXP for your stored process, you would have to tell WRS or PPT folks that they cannot use your SP. And, of course, Microsoft Word only "accepts" SASReport XML, RTF and HTML results from a stored process, so it wouldn't "accept" TAGSETS.EXCELXP output, either. Microsoft Excel (using the SAS Add-in for Microsoft Office) will ONLY accept SASReport XML, HTML and CSV results from a stored process. Yes, you're right, TAGSETS.EXCELXP is NOT in that list.

6) The only client apps which can submit a stored process and receive results from a stored process that uses TAGSETS.EXCELXP are 'web-based' client apps that receive "streaming" output -- such that you can change the content-type header, as mentioned in #4. Those client apps would be: the Information Delivery Portal or the Stored Process Web Application (SPWA).

7) If you note how things work in EG, you can't automatically "turn on " TAGSETS.EXCELXP for output without doing something with code or extra options. That's because EG is very friendly with the SASReport XML flavor of XML -- SASReport XML is an XML description of a multi-section report -- such as that used by Web Report Studio. And EG can also create SASReport XML -- so that you might be able to deploy reports from EG for opening with WRS (or other client apps) using a common format.

8) TAGSETS.EXCELXP is a destination that produces Microsoft-specific XML (Spreadsheet Markup Language XML -- also known as Excel XML 2003). TAGSETS.EXCELXP follows the rules of Microsoft for Spreadsheet ML 2003. TAGSETS.EXCELXP is a different "flavor" of XML than SASReport XML. The SAS BI Platform uses SASReport XML very happily. TAGSETS.EXCELXP -can- be used in the context of the BI Platform client applications, but if you want to use TAGSETS.EXCELXP for your stored processes, you are creating a stored process that can only be used/invoked by a limited number of client applications.

If you search in the Stored Process Forum, you will find several examples of code for creating a stored process that uses TAGSETS.EXCELXP. Some of them use %STPBEGIN/%STPEND and some of them use _WEBOUT.

Sorry to post such a long explanation. I wanted to clarify things now in order to set the correct expecations for what you can and can't do with a stored process and TAGSETS.EXCELXP.

cynthia
Super Contributor
Posts: 275

Re: Missing tabs in Excel while using ExcelXp tagsets

Posted in reply to Cynthia_sas
Many thanks Cynthia for the explanation. Are there papers that show how to use _WEBOUT and STPSRV_HEADER to get the desired output?

I am also using your paper - Tiptoe through templates to build a combination of Proc Template/Proc Report/ Excel Xp tagsets to enhance a bunch of existing stored process' excel output.

Thanks,
SAS Super FREQ
Posts: 8,866

Re: Missing tabs in Excel while using ExcelXp tagsets

Hi:
I think I posted a few examples in the Stored Process Forum. Other than the documentation, I'm not sure of any other places to look. I think there might be some other papers -- you have to look specifically for papers about Stored Processes (because the SAS/IntrNet suite also used _WEBOUT).

Although this addition to one of my stored process papers has some useful information about where to store templates if you are updating templates on the BI Platform:
http://support.sas.com/rnd/papers/regional08/SP_Template.pdf (Although the handout specifically discusses a changed tagset template, the same storage concepts will also apply to a changed style template.)

cynthia
Super Contributor
Posts: 275

Re: Missing tabs in Excel while using ExcelXp tagsets

Posted in reply to Cynthia_sas
Thanks - I will go through the pdf.
Quick question on the writing the proc template code. Why do I see some of the SAS keywords in red when the syntax is correct. I copied the code from a SAS sample code note and as soon as I make changes, the "STYLE" keywords turn to red?


proc template;
define style customstyle;

/* Use Styles.Default as the basis for the customized */
/* style definition. */

parent=styles.default;

/* Change the foreground color of the text of TITLE */
/* statements to a nondithering red. */

Style SystemTitle from SystemTitle
"Controls system title text." /
Foreground=#FF0000
;

/* Change the foreground color of the text of FOOTNOTE */
/* statements to a nondithering blue. Change the font */
/* to a small, italic font. */

Style SystemFooter from SystemFooter
"Controls system footer text." /
Foreground=#0000FF
font=("Arial, Helvetica, Helv", 2, italic)
;

/* Change the foreground color of the text of the */
/* procedure title to white. Change the background */
/* color to a nondithering green. */

Style ProcTitle from ProcTitle
"Controls procedure title text." /
ForeGround = #FFFFFF
backGround = #00FF00
;

/* Modify attributes of the table that presents the */
/* procedure results. Increase the width of the border. */
/* Change the border color to black. Increase the */
/* amount of space between the cells of the table. */
/* Increase the amount of space between the data in each */
/* cell and the edge of the cell. Remove all rules. */
/* Change the background color to a nondithering yellow. */
/* Note that the table background shows through between */
/* the cells but is not obvious where the background */
/* color of the cells matches the background color of */
/* of the table. */

Style Table from Output
"Abstract. Controls basic output forms." /
BorderWidth = 20
BorderColor = #000000
CellSpacing = 10
CellPadding = 20
Frame = BOX
Rules = NONE
BackGround = #FFFF00
;

/* Change the foreground color of the text of the */
/* cells that contain data to a nondithering blue. */
/* Change the background color to a nondithering yellow. */

Style Data from data
"Default style for data cells in columns." /
ForeGround = #0000FF
BackGround = #FFFF00
;

/* Change the foreground color of the table headers to */
/* black. Set the background to a nondithering pink. */

Style Header from Header
"Controls the headers of a table." /
Foreground = #000000
Background = #FF00FF
;

/* Change the foreground color of the cells that are */
/* row headers to a nondithering yellow. Change the */
/* background color to the same blue that is the */
/* foreground color for the cells that contain data. */
/* Thus, the cells that contain data and the cells that */
/* are row headers use inverse colors. */

Style RowHeader from RowHeader "Controls row headers." /
ForeGround = #FFFF00
Background = #0000FF
;
end;


/* Run the procedure to create the custom style definition. */

run;

Sorry for being too trivial. Smiley Sad
SAS Super FREQ
Posts: 8,866

Re: Missing tabs in Excel while using ExcelXp tagsets

Hi:
No worries. In class we explain that not all the keywords made it into the enhanced editor's color coding routine.

If you see red with PROC TEMPLATE code, that is not a good indicator of a problem. You have to run the code and check the log for error messages issued when the TEMPLATE code is compiled, if there are errors.

cynthia
Ask a Question
Discussion stats
  • 7 replies
  • 476 views
  • 0 likes
  • 2 in conversation