Exploring tagsets.excelxp for existing templates

[deleted_user]

I need to use ods tagsets.excelxp for creating a report using an existing template.
The template contains all the header information in a required format.
What option should I use to output my dataset values into those specific cells (meant for values in the template)???

[deleted_user]

Thanks cynthia for your suggestion fo starting a new post.Please elaborate on your point for "You might try to replicate your header information using the TAGSETS.EXCELXP options to set PRINT headers and footers."

[Cynthia_sas]

Hi:
If you run your step with the DOC= suboption:
[pre]
ods tagsets.excelxp file='xxx.xml'
options=(doc="Help");
...
[/pre]

...you will see in the SAS log what suboptions are available for inserting Microsoft information into the Excel print headers and footers.

There are also suboptions to control whether the SAS TITLE/FOOTNOTE information is used in the print header or embedded into the body of the spreadsheet. This paper has some good examples:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia

[deleted_user]

Hi cynthia, many thanks.
Although I have already tried looking in help.
Is there any way to ensure that the excel file created by tagsets.excelxp has version 2003, like the one we have for excel engine?

[Cynthia_sas]

Hi:
If you investigate on the Microsoft site, you will discover that Spreadsheet Markup Language was introduced with Office 2002/2003. The XML for Office 2007 is different from Spreadsheet Markup Language XML for 2003.

What ODS TAGSETS.EXCELXP creates is -only- Spreadsheet Markup Language XML that conforms to the Office 2002/2003 specification for XML, as described here:
http://support.sas.com/kb/33/564.html
and the Microsoft description of Spreadsheet ML is here:
http://msdn.microsoft.com/en-us/library/bb226687(office.11).aspx
where it says:
"SpreadsheetML is an XML dialect developed by Microsoft to represent information in an Excel workbook. This column, the first of a two-part series, explores the concept, schema definition, and some scenarios in which it makes sense to programmatically generate Excel workbooks using Office 2003 and SpreadsheetML. "

You don't need any other choices. Office 2002/2003/2007 will open SpreadsheetML as created by ODS. Before Office 2003, there was only an HTML spec -- that is created by SAS with ODS MSOFFICE2K tagset destination, an entirely different destination and file type This HTML file can be opened with both Word and Excel in Office 2000 and higher. SpreadsheetML XML, as created by ODS TAGSETS.EXCELXP, can only be opened with Excel (or with an application that knew how to render the XML -- not sure there are any other than Excel).

Remember that when you use ODS, you are not really doing an "export" such as you do with the LIBNAME engine or PROC EXPORT. ODS is creating an ASCII text file that conforms to a Microsoft specification. Microsoft Excel knows how to open and render the information in that file.

cynthia