The SAS Output Delivery System and reporting techniques

ExcelXP - questions on adding and moving items

Reply
N/A
Posts: 0

ExcelXP - questions on adding and moving items

Hello!

I am struggling along with using ODS tagsets (ExcelXP) and was wondering if someone could share with me their insights.

I am using SAS 9.1.3 SP4 (Win 32bit) and the latest ExcelXP version 1.86.

A few questions..

1) Is it possible to add images to a new worksheet? If so, how does one do that? I just have 2 small logos I would like to add to the bottom right and left hand corners.

2) I have the paper Paper 016-2009 A SAS® Output Delivery System Menu for All Appetites and Applications (http://support.sas.com/resources/papers/proceedings09/016-2009.pdf)
about using contents= to hide the lower levels. It seems contents = is a 9.2 feature and for the life of me I do not really follow the example of using proc template on page 3 and try to apply it to the excelXP tagset. That is a new proc for me and after reading about it is still not 100% clear.

3) Is it possible to write out formatted titles to given areas within a worksheet, i.e. Bold, Verdana and in the centre? Is it right to say a tagset change is the only way? I saw this earlier post: http://support.sas.com/forums/thread.jspa?threadID=542

I have alot of papers and gone through the help but would be very grateful for some guidance.

cheers!
DSS
SAS Super FREQ
Posts: 8,743

Re: ExcelXP - questions on adding and moving items

Hi:

Here's some advice/feedback:

1) Graphs or logos in TAGSETS.EXCELXP output: No -- by Microsoft design images are not allowed to be inserted into Spreadsheet Markup Language XML output.

If you want/need to use images (whether logos or SAS-generated graphs) and you need to open your output files in Excel, then your only choices to create output are to use ODS and HTML-based destinations (which does support images) -- ODS HTML3, ODS HTML, ODS MSOFFICE2K, etc. Or, to create your Workbook with TAGSETS.EXCELXP, manually save the workbook to an Excel format (like Excel .XLS) (or use a VB script or Excel macro) and -then- insert your logo into the workbook/worksheets. Remember that when you use ODS to create output for Excel, you are NOT creating true, binary Excel (.XLS) files. You are creating ASCII text files (either XML or HTML) that Excel knows how to open and render. If you do not like the HTML methods or the Spreadsheet ML (TAGSETS.EXCELXP) methods, then you must move outside of ODS to DDE or OLE-DB methods to create your workbook.

2) In the paper you reference, the example on page 3 shows how to make changes to TAGSETS.HTML4 in order to create TAGSETS.TEST with Table of Contents modifications. This approach is DESTINATION-specific. HTML is NOT the same as Spreadsheet Markup Language XML (which is what's created by TAGSETS.EXCELXP). So I would not expect the changed tagset template to work if you tried to use it. One of the reasons that Microsoft came up with Spreadsheet Markup Language XML to describe a workbook and worksheets within the workbook is that they were bound by the W3C specifications for HTML and what HTML could do and could define. An HTML page defines 1 and only 1 page to be rendered in a browser, no matter how many tables are shown on that page. An XML definition of a Workbook and Worksheets, however, could define multiple sheets in one workbook. That is one reason why folks like TAGSETS.EXCELXP -- the ability to create multi-sheet workbooks from their SAS procedure output.

If you want CONTENTS with your TAGSETS.EXCELXP output, you need to do this (note the use of sub-option CONTENTS='yes').:
[pre]
ods tagsets.excelxp file='c:\temp\testtoc.xls'
options(doc='Help' contents='yes') style=sasweb;

proc print data=sashelp.class(obs=3);
title 'The Title will be in the Header Area';
run;

proc print data=sashelp.shoes(obs=50);
title 'Selected Sales for Shoes in Canada';
run;

proc tabulate data=sashelp.shoes;
title 'What Are the Product Sales';
class product/style={cellwidth=1.5in};
var sales;
table product all,
sales=' '*(min mean median max);
run;

ods _all_ close;
[/pre]

If you look in the SAS log after this job is finished, you will see documentation on the other sub-options that you can use with TAGSETS.EXCELXP. This paper is also very useful on using the "printing" level sub-options:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

3) Titles/centering, etc. work for me in SAS 9.2 with TAGSETS.EXCELXP version v1.94 and WITHOUT a tagset template change (In fact, I rarely ever advise my students to undertake TAGSET template changes for use with TAGSETS.EXCELXP -- Spreadsheet Markup Language XML is complicated, it is a Microsoft specification and I am happy that the developers have figured out how to make it work with ODS -- and when pressed, I find that most of my students really want to do style-related things in their worksheets that are mostly all do-able with techniques as I show below, with STYLE= overrides or with STYLE template changes):
[pre]
ods tagsets.excelxp file='c:\temp\testtitle.xls' style=sasweb
options(embedded_titles='yes');

proc print data=sashelp.class(obs=3);
title f='Verdana' h=8pt c=cyan 'The Title';
run;

ods _all_ close;
[/pre]

The above code uses a TAGSETS.EXCELXP sub-option of embedded_titles='yes' to make the SAS title go into the worksheet itself instead of the header area of the worksheet. The other options on the TITLE statement are options that have worked with ODS since SAS 8.2 (and earlier, I believe). F= specifies a font;H= specifies a size and C=specifies a color -- there is no need to explicitly bold the title because SAS titles are bolded by default.

I do appreciate the fact that you have done your research by looking for previous forum postings and papers to help you out. I fear you've started in the middle of the neighborhood with some of these papers, however. You may need to look at a more birds-eye view map to figure out where you are and where you're going and read some overviews to figure out how to get there.

For an overview of what SAS templates are and what they can do, this is one of my SGF papers:
http://support.sas.com/resources/papers/proceedings09/227-2009.pdf

More about style templates:
http://support.sas.com/resources/papers/proceedings10/033-2010.pdf

And, now, my standard disclaimer: When you use ODS to create files for Excel, you are NOT, NOT, NOT creating true, binary Excel files. You are merely creating ASCII text files in various formats -- and Excel knows how to open and render these ASCII text files. Your choices for using ODS to create files that Excel can open and render are these 3 markup language types of files:

1) CSV-based: ODS CSV, ODS CSVALL create CSV (comma-separated value files) that Excel knows how to open and render.
2) HTML-based: ODS HTML, ODS MSOFFICE2K, ODS CHTML, ODS PHTML, etc all create HTML files that Excel knows how to open and render.
3) XML-based: ODS TAGSETS.EXCELXP creates Spreadsheet Markup Language XML files that Excel knows how to open and render.

An overview of Markup Languages (like HTML and XML and how they work with SAS):
http://support.sas.com/rnd/papers/sugi29/markup-basics.pdf

cynthia
N/A
Posts: 0

Re: ExcelXP - questions on adding and moving items

Many Thanks for the details Cynthia. I do recall reading about the issue around images and SpreadsheetML XML now.

I did experience some issues around running your samples but will put that down to the different SAS versions. I will do some further reading and testing. Once again, Thanks for your help.

Cheers,
DSS
Ask a Question
Discussion stats
  • 2 replies
  • 175 views
  • 0 likes
  • 2 in conversation