SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Tagset Event Timing???

Occasional Contributor
Posts: 7

Tagset Event Timing???



I am trying to write a tagset that can I can call several times and pass a new set of ODS parameters for each call of the ODS Tagset statement. I am having some trouble with setting up the order of events to be able to call an event that will ALWAYS execute with each call of the ODS statement regardless if there is a SAS procedure after the ODS statement.

In this Main Event the tagset would branch off to various Excel events:

* Excel_Workbook event which creates a new workbook

* Excel_Worksheet event which create a new worksheet and sets the
excel options.

* Excel_Chart event which creates a new chart and places the chart
on a worksheet and sets the chart options.

Possible Scenario

a. On the 1st ODS statement call:

* get ODS options and create internal memory variables like $my_variable
* open a stream variable
* pass commands to the stream:
* create an Excel workbook.
* create an Excel worksheet.
* set Excel options.
* create a chart object.
* place the chart object on a worksheet.
* set chart options.
* close stream variable

b. the 2nd to nth ods call ( Chart Object Creation )
* open the stream variable
* pass commands to the stream
* create a new worksheet (opt.)
* set excel options.
* create a chart object
* set chart options
* close stream variable

c. On the last ods call before the closing ODS statement

* write the contents of the stream to the html document

d. The Closing ODS Destination call

Example SAS Code:

1. ODS Tagsets.My_Tagset File="d:\class.xls"
options (
excel_create = "yes"
excel_sheet = "class1,class2"

2. Proc print data=class1;

3. Proc print data=class2;

4. ODS tagsets.my_tagset file="d:\class.xls"
chart_layout_style = "LA22" ( Four charts in Quadrant Layout )
chart_sheet = "Master_Charts"
chart_anchor_location = "1"
chart_title = "my Chart Title1"
chart_series = "class1!a:f"

5. ODS tagsets.my_tagset file="d:\class.xls"
chart_layout_style = "LA12" ( Landscape Letter 2 charts side by side )
chart_anchor_location = "2"
chart_sheet = "Master_Charts"
chart_title = "my Chart Title2"
chart_series = "class2!a:f"

One idea ( Not my Favorite ) is to:

* place a dummy Proc Print after each ODS statement to force a
PROC event to be triggered.

* Place a trigger statement inside the PROC event to call the Initialize event
that will get new ODS options and create internal memory variables.

* Place a trigger statement inside the PROC event to call the Excel events
to generate the script commands to handle the creation of the Excel objects
and the setting of the Excel properties using activex controls.

Any thoughts would be greatly appreciated!

Thanks Vic


My client wants to maintain the SAS to Excel / Chart Reporting.

They currently create sas tables and export them to an excel file.

They do a lot of manual copy/paste to a presentation Excel workbook.

There is no consistant way they get the data from sas to Excel.

* sometimes its DDE
* sometimes its standard ODS
* sometimes its with the excel libname engine.

The client wants to create the Excel files entirely from within sas and they do
not want to use model Excel Files

This is where the ODS tagset comes in...

There are two possible tagsets that we can use as a base as both tagsets
would require enhancement:

1. ODS Tagset TableEditor uses Activex controls ( VBA script commands )

2. ODS Tagset ExcelXP uses XML tags and extending it with
Webchart XML tags.
Posts: 9,040

Re: Tagset Event Timing???

By Microsoft design, SpreadsheetML XML -- which is the type of markup created by TAGSETS.EXCELXP does NOT support the inclusion of any type of graphics. If you try to create graphic output using TAGSETS.EXCELXP as the ODS destination, you should see this note in the log:

Excel XML does not support output from Proc:Gchart
Output will not be created.

That means you will NOT be able to use TAGSETS.EXCELXP to create workbooks which contain graphic output. So, if you want a tagset template to use as the basis for your new tagset template, you might start with TAGSETS.TABLEEDITOR, which, I believe is an HTML-based tagset with VBScript embedded in the HTML.

As for your opening question about event timing.... generally, the ODS process or SAS procedure determines which event is called and when it is called. You can deal with what you want to do in a few different ways:

There are some examples here. Specifically, Example 4 shows how to trigger events -- so, if you know that the DOC event will always be first, you could trigger an event from the DOC event.

Or, you could create an event that would get triggered when you pass an option to your tagset:
ods tagsets.wombat file='';
*** proc1;
ods tagsets.wombat options(dosomething='yes');
*** proc 2;
ods tagsets.wombat options(diffopt='xyz');
ods tagsets.wombat close;

Also, as described in the documentation, here: can specify EVENT= in the ODS MARKUP statement, which would give you the control to trigger a specific event.

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation