2月 182013


I personally nominated SAS ODS Report Writing Interface was the one of the best technology I found in SAS in 2012. It can generates reports cell by cell and row by row and has much flexibility to produce highly customized reports. Basically, to use it,

  • first assign an ODS destination. Nothing new, and I prefer HTML like

ods html   file="output.html" style=sasweb;

  • then create a object(instance) based on the ODS Report Writing class, odsout,

declare odsout myout();

ODS Report Writing Interface holds some so called object oriented features. Odsout is a predefined class (or, a SET, a CONTAINER), then you take from it a instance or an object, myout(or any other legal SAS variable names). Suppose Odsout is a class for apples, then myout is a specific apple.

  • apply methods associated with the Odsout class, like CELL, ROW, TABLE to generate reports.

Roughly methods are just the functions (subroutines, procedures) in the object oriented world. For example, in SAS Data Steps, you use function weight(apple) to get the weight of the apple; in object oriented world, you use apple.weight() to return the same thing. In ODS Report Writing Interface, if you want to get a table, use TABLE methods:

    • myout.TABLE_START() to start a table
    • myout.TABLE_END to end a table

Then all we need to do next is to use the flexible SAS data steps to leverage the ODS Report Writing Interface methods (see docs). The codes, you can see below, are pretty verbose compared to its counterparts, PROC REPORT, but that’s why it gains power to build highly customized reports. It’s also very structural (and easy to build, like playing blocks):

/*making a sortable HTML table*/
%macro ods_html_sort_table;
    <script src=’
    <script src=’
    <script>$(document).ready(function(){$(‘.table’).tablesorter({widgets: ['zebra']});});</script>

title ;
ods listing close;
ods html   file="a:\test\iris.html" style=sasweb headtext="%ods_html_sort_table";

data _null;
    set sashelp.iris;
    by Species;
/*    create an object, obj, based on the ODS Report Writing class, odsout*/
    if _n_ = 1 then do;
        dcl odsout obj();

    if (first.Species) then do; *by group processing;
       obj.title(text: "Fisher’s Iris Data Set by Species"); *title;

/*       start a table*/
              if (Species = "Setosa") then
                 obj.image(file: "Iris_setosa.jpg" );*insert image;
              else if (Species = "Versicolor") then
                 obj.image(file: "Iris_versicolor.jpg" );
              else if (Species = "Virginica") then
                 obj.image(file: "Iris_virginica.jpg" );

                obj.format_cell(text: "Iris Species",  overrides: "fontweight=bold just=right" );
                obj.format_cell(text: Species, column_span: 3, overrides: "just=left");

                obj.format_cell(text: "Unit",  overrides: "fontweight=bold just=right" );
                obj.format_cell(text: "(mm)", column_span: 3, overrides: "just=left");

       /* start another table */
                    obj.format_cell(text: "Sepal Length" , overrides: "fontweight=bold");
                    obj.format_cell(text: "Sepal Width" , overrides: "fontweight=bold");
                    obj.format_cell(text: "Petal Length" , overrides: "fontweight=bold");
                    obj.format_cell(text: "Petal Width" , overrides: "fontweight=bold");

            obj.format_cell(data: SepalLength);
            obj.format_cell(data: SepalWidth);
            obj.format_cell(data: PetalWidth);
            obj.format_cell(data: SepalLength);

    if (last.Species) then do;

        obj.note(data: "Note: These Tables are Sortable."); *note;

        obj.foot_start(); *footer;
                    obj.format_text(data: "Footer: Data from SAS V&sysver at &sysscp &sysscpl Sashelp.iris",just:"C");


ods html close;
ods listing;


  • A flavor added to get a sortable HTML report. Thanks to Charlie Huang and then Andrew Z to introduce a Javascript library JQury to SAS HTML report.
  • The full report, see here.
  • If column spannings needed, use the following codes as header (and the report here):
  • obj.head_start();
            obj.format_cell(text: "Sepal" , overrides: "fontweight=bold",column_span: 2);
            obj.format_cell(text: "Petal" , overrides: "fontweight=bold",column_span: 2);

            obj.format_cell(text: "Length" );
            obj.format_cell(text: "Width" );
            obj.format_cell(text: "Length" );
            obj.format_cell(text: "Width" );

  • ODS Report Writing Interface will get rid of its preproduction hat since SAS 9.4, but you can use it somehow since SAS 9.1.3. For more, see the draft SAS 9.4 ODS documentation with ODS Report Writing Interface.
  • To get started, see the developer’s paper.
1月 062013

Hate it or not (Yes I do), SAS programmers can’t just get rid of Microsoft Office Excel in their life.

Now my turn (with Tagsets.ExcelXP, I can at least get rid of DDE)…

0. SAS Templates window ODSTEMPLATES

A visual way to browse all ODS templates is to use windows command “ODSTEMPLATES” to invoke a SAS template window.

1.  Check the current ExcelXP tagset version

filename temp temp;
ods tagsets.ExcelXP file=temp options(doc=’help’);
ods tagsets.ExcelXP close;

Scroll down the Log window to the end and you get something like:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122, 01/04/2011).

2. Get the source codes of current ExcelXP tagset and library it’s stored

proc template;
    source Tagsets.ExcelXP;

The Log window shows the template codes:

15   proc template;
16       source Tagsets.ExcelXP;
define tagset Tagsets.ExcelXP;
   parent = Tagsets.ExcelBase;
NOTE: Path ‘Tagsets.ExcelXP’ is in: SASHELP.TMPLMST.

Note Tagsets.ExcelXP is subject to Tagsets.ExcelBase.

The template store path uses a two level name in which SASHELP is a SAS library and TMPLMST, a name assigned to distinguish a set of templates. You can also store your customized templates in SASHELP.mytpl1, WORK.forTLF and such.

If want a the source codes in a separated file rather than in Log window, try

proc template;

source Tagsets.ExcelXP/file="a:\test\ExcelXP.tpl";

The extension doesn’t matter and you can use anything you like (including .SAS).

3. List all ODS template store path

ods path show;


Current ODS PATH list is:


This order also indicates the ODS template searching priority, first SASUSER.TEMPLAT then SASHELP.TMPLMST. If two templates with same name exist in both library, the one in SASUSER.TEMPLAT will be used.

Templates in SASHELP.TMPLMST can’t be modified(read only).

4. Install latest version in SASUSER library

The last version of ExcelXP tagset is v1.127 by far according to SAS ODS MARKUP portal:

filename excltags url ‘http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl’;
%include excltags;


NOTE: TAGSET ‘Tagsets.ExcelBase’ has been saved to: SASUSER.TEMPLAT
NOTE: Overwriting existing template/link: Tagsets.Config_debug
NOTE: TAGSET ‘Tagsets.Config_debug’ has been saved to: SASUSER.TEMPLAT
NOTE: TAGSET ‘Tagsets.ExcelXP’ has been saved to: SASUSER.TEMPLAT

Since there is already an ExcelXP tagset in SASHELP.TMPLMST and it can’t be overwritten, this new added ExcelXP tagset is thrown to SASUSER.TEMPLAT by adding a STORE option (check the source code) and will be used when statement “ODS tagsets.ExcelXP” invoked.

5. List all templates

proc template;
    list / store=sasuser.templat;
    list / store=sashelp.tmplmst;

Get output:


If only type “Tagset” need (to save space!):

proc template;
list tagsets/store=sasuser.templat;


6. Delete template

proc template;
    delete Tagsets.ExcelBase;
    delete Tagsets.ExcelXP;

It’s always safe to perform delete statements since items in SASHELP.TMPLMST are read only.  In this case, the ExcelXP tagset in SASUSER.TEMPLAT will be deleted.


8617   proc template;
8618       delete Tagsets.ExcelBase;
NOTE: ‘Tagsets.ExcelBase’ has been deleted from: SASUSER.TEMPLAT
8619       delete Tagsets.ExcelXP;
NOTE: ‘Tagsets.ExcelXP’ has been deleted from: SASUSER.TEMPLAT

check again following step #5 and get what’s in SASUSER.TEMPLAT (no ExcelXP tagset anymore):


7 Install in other specified library

Install ExcelXp tagset in a other library. It will be deleted if current SAS session is killed:

libname excltags ‘a:\test’;
ods path (prepend) excltags.templat(update);
filename excltags url ‘
%include excltags;


NOTE: Overwriting existing template/link: Tagsets.ExcelBase
NOTE: TAGSET ‘Tagsets.ExcelBase’ has been saved to: EXCLTAGS.TEMPLAT
NOTE: Overwriting existing template/link: Tagsets.Config_debug
NOTE: TAGSET ‘Tagsets.Config_debug’ has been saved to: EXCLTAGS.TEMPLAT
NOTE: Overwriting existing template/link: Tagsets.ExcelXP
NOTE: TAGSET ‘Tagsets.ExcelXP’ has been saved to: EXCLTAGS.TEMPLAT

show path as step #3:

Current ODS PATH list is:


If apply step #6 to delete ExcelXP tagset, the items in EXCLTAGS.TEMPLAT will be deleted(as same as the searching priority).

8. Hello World

Adopted from one of Vince DelGobbo’s papers(2012):

title ‘The CLASS Data Set’;
footnote ‘(From the SASHELP Library)’;

*Set some "global" tagset options that affect all worksheets;

ods tagsets.ExcelXP path="a:\test\" file=’MyWorkbook.xml’ style=Printer
                            print_header=’&C&A&RPage &P of &N’
                            print_footer=’&RPrinted &D at &T’

%macro doit;

proc sort data=sashelp.class (keep=sex) out=class nodupkey;
    by sex;

data _null_;
    set class end=eof;
    call symputx(‘sex’||II,compress(sex));
    if eof then call symputx(‘total’,II);

%do i=1 %to &total;
    ods tagsets.ExcelXP options(sheet_name="Students_&&sex&i");

    proc print data=sashelp.class noobs style(Header)=[just=center];
      where (sex eq "&&sex&i");
      var name age      / style(Column)=[background=#99ccff];
      var height weight / style(Column)=[background=#99ccff tagattr='format:#.0'];


%mend doit;


ods tagsets.ExcelXP close;

This .XML file can be opened by Office Excel:


9. Get Help

As a new comer, I found the following materials useful and accessible:

12月 142012
This week's SAS tip is from Lauren Haworth, Cynthia Zender, and Michele Burlew's book Output Delivery System: The Basics and Beyond. This mega book is packed with multiple examples that can help all levels of SAS users. If you're using ODS, take a look at these previously featured tips from the book: [...]
9月 232012

It is said in An Introduction to R, one of R official documents (Current Version: 2.15.1):

There is an important difference in philosophy between S (and hence R) and the other main statistical systems. In S a statistical analysis is normally done as a series of steps, with intermediate results being stored in objects. Thus whereas SAS and SPSS will give copious output from a regression or discriminant analysis, R will give minimal output and store the results in a fit object for subsequent interrogation by further R functions.

For SAS, it’s true, but not exactly the whole story. Actually all outputs generated by SAS procedures are stored in datasets besides the “copious output” in Output Window for further processing if needed. It is totally controllable like object in R. Take a regression example, You can use ODS Select statement to select what you want to be displayed in Output Window, and ODS Output statement to fetch the output datasets(test on SAS 9.3M1 in Windows 7):

ods select Anova ResidualPlot;
ods output Anova=Anova;

proc reg data=fitness;
   model Oxygen=Age Weight RunTime RunPulse RestPulse MaxPulse / selection=forward; 

or, you can use ODS Exclude statement to just drop some outputs in Output Window:

ods exclude ResidualPlot;

proc reg data=fitness;
   model Oxygen=Age Weight RunTime RunPulse RestPulse MaxPulse / selection=forward; 

To get a list all the ODS outputs like Anova, ResidualPlot, you can trigger on ODS Trace ON statement:

ods trace on;

proc reg data=fitness;
   model Oxygen=Age Weight RunTime RunPulse RestPulse MaxPulse
         / selection=forward; 

ods trace off;

then you get a full list in Log Window like:

Name:       NObs
Label:      Number of Observations
Template:   Stat.Reg.NObs
Path:       Reg.MODEL1.SelectionMethod.Oxygen.NObs

Output Added:
Name:       ANOVA
Label:      ANOVA
Template:   Stat.REG.ANOVA
Path:       Reg.MODEL1.SelectionMethod.Oxygen.Step1.ANOVA

Output Added:
Name:       SelParmEst
Label:      Parameter Estimates
Template:   Stat.REG.SelParmEst
Path:       Reg.MODEL1.SelectionMethod.Oxygen.Step1.SelParmEst

4月 302012
During the SAS Global Forum this past week, SAS offered several demos on mobile technology including: Michael Hecht, SAS, JMP iPad App Super Demo Chris Hemedinger hosted a Live Tech Talk on Tuesday that covered the new SAS Visual Analytics Explorer with demos on Microsoft Surface and iPad Apps Remember everyone is using mobile devices [...]
4月 152012
ODS Graphics have matured.  With SAS 9.2, GTL and SG procedures were a new direction for creating analytical graphs in SAS. The motivation and design of the GTL framework and the SG procedures was driven primarily by the needs of the procedure writers within SAS to enable the automatic creation [...]
3月 092012
This week's tip comes from three authors who've made a big impact within the SAS user community. Lauren Haworth, Cynthia Zender, and Michele Burlew partnered up to write Output Delivery System: The Basics and Beyond a couple of years ago-and their book remains a bestselling go-to guide for anyone wanting to learn more about ODS. [...]
2月 282012
Neil Constable is a Principal Education Consultant at SAS in the United Kingdom, where he applies his extensive knowledge of Base SAS, SAS Enterprise Guide, and the SAS business intelligence tools. He's also the author of SAS Programming for Enterprise Guide Users, Second Edition--and this week's featured tip. You can get to know Neil [...]