ODS Tagsets

3月 272015
 

SAS Technical Support Problem SolversIn Microsoft Excel, a PivotTable can help you to create an interactive view of summarized data. Within a PivotTable, it’s easy to adjust the dimensions (columns and rows) and calculated measures to suit your ad-hoc reporting needs. You can also create a PivotChart – similar in concept to a PivotTable, but using a visualization technique such as a bar chart instead of a table.

SAS provides a special ODS tagset that can add a PivotTable or a PivotChart to your Microsoft Excel workbook automatically. It’s called the TableEditor tagset, and you can download it for free from the SAS support site.

In the example in this post, we’ll use the ODS EXCEL destination to create a native Open Office XML file (XLSX file) for Excel to read. Then we’ll use the TableEditor tagset to update the workbook to add a PivotTable to this worksheet.

How to access the TableEditor tagset

You can automate PivotTable creation by using the downloadable TableEditor tagset on the Windows operating system. If your network allows you to access the Web from within your SAS session, you can even use %INCLUDE to access the tagset directly within your program:

/* reference the tagset from support.sas.com */
Filename tpl url 
"http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";

/* insert the tagset into the search path for ODS templates */
Ods path(Prepend) work.templat(update);
%include tpl;

If you cannot use FILENAME URL, then simply download the TPL file to a local folder on your PC, and change the %INCLUDE statement to reference the file from that location.

Creating the Excel workbook

This latest version of the tagset allows you add a PivotTable to any data source that Excel can read, regardless of how that Excel file was created and without having to generate an intermediate HTML file. SAS allows you to create Excel content using several methods, including:

  • ODS CSV (or simple DATA step) to create a comma-separated value representation of data as a source for an Excel worksheet
  • ODS tagsets.ExcelXP, which creates an XML representation of a workbook that Excel can read.
  • PROC EXPORT with DBMS=EXCEL, EXCELCS, or XLSX (requires SAS/ACCESS to PC Files)
  • ODS EXCEL (new in SAS 9.4 and still labeled “experimental” as of SAS 9.4 Maintenance 2)

Here’s program to create the Excel content from the SHOES sample data. Change the “temp” file path as needed for your own system.

ods excel file="c:temptemp.xlsx" options(sheet_name="shoe_report");

proc print data=sashelp.shoes;
run;

ods excel close;

Here’s a sample of the output Excel workbook.

Excel workbook created with SAS ODS

Working with TableEditor tagset options for PivotTables

The TableEditor tagset has options to control the various drop zones of an Excel PivotTable such as:

  • Page area -  uses one or more fields  to subset or filter the data .
  • Data areaa field that contains  values to be summarized.
  • Column areaa field to assign to a column orientation in the PivotTable.
  • Row areaa field that you assign to a row orientation which is used to categorize the data.

The options to control these drop zones in the PivotTable are the PIVOTPAGE=, PIVOTROW=, PIVOTCOL= and PIVOTDATA= options. Each of these options can specify a single column or multiple columns (each separated with a comma).

The UPDATE_TARGET= option contains the name of the workbook to update, while the SHEET_NAME= option which specifies which sheet should be used as the source for the PivotTable. The OUTPUT_TYPE= option is set to “Script”, which tells the tagset to create a JavaScript output file with the Excel commands. Other options can control formatting, appearance, and the summarizations of the PivotTable.

NOTE: when specifying a file path in the UPDATE_TARGET= option, you must “escape” each backslash by using an additional backslash. The backslash character has a special meaning in JavaScript that turns special characters into string characters.

Here are some notes on the remaining options:

  • PIVOT_SHEET_NAME= (new option) allows you to name the PivotTable to be named different from the source worksheet name. (The default is to append “_Pivot” to the source worksheet name.)
  • PIVOT_TITLE= (new option) adds a title to the PivotTable.
  • PIVOTDATA_FMT= specifies the numeric display format
  • PIVOT_FORMAT= specifies one of the Excel table formats (found on the formatting style ribbon).

Creating the PivotTable

We’ll use a two-step technique to add a PivotTable to our sample workbook:

  1. Use ODS tagsets.TableEditor and special PIVOT options to create a script file that contains instructions for the PivotTable that we want.
  2. Use the X command to execute that script file, which will automate Microsoft Excel to add the PivotTable content.

Here’s a program that generates the script and executes it.  The NOXSYNC and NOXWAIT options allow control to return to the SAS session as the script is run.

options noxsync noxwait;
ods tagsets.tableeditor file="c:tempPivotTable.js"                                                                                                                                      
/* remember to escape the backslashes */
  options(update_target="c:\temp\temp.xlsx" doc="help"                                                                                                                                 
    output_type="script"                                                                                                                                           
    sheet_name="shoe_report"  
    pivot_sheet_name="Profit Analysis" 
    pivotrow="region"                                                                                                                                              
    pivotcol="product"                                                                                                                                             
    pivotdata="sales"  
    pivotdata_fmt="$#,###" 
    pivot_format="light1"
    pivot_title="Pivot Analysis for XXX" 
);                                                                                                                                             

/* dummy output to trigger the file creation */                                                                                                                                                                                          
data _null_;                                                                                                                                                                          
 file print; 
 put "test";                                                                                                                                                                             
run;                                                                                                                                                                            
                                                                                                                                                                  
ods tagsets.tableeditor close; 
x "c:tempPivotTable.js";    

Here is a sample of the PivotTable output.

pivot2

Creating PivotCharts

What’s a good PivotTable without a PivotChart?  PivotCharts can be added to an existing workbook as well. Simply add the PIVOTCHARTS=”yes” option along with the CHART_TYPE option.

options noxsync noxwait;
ods tagsets.tableeditor file="c:tempPivotChart.js"     
  /* remember to escape the backslashes */ 
  options(update_target="c:\temp\temp.xlsx"                                                                                                                                  
    output_type="script"                                                                                                                                 
    sheet_name="shoe_report"  
    pivot_sheet_name="Profit Analysis Chart" 
    pivotrow="region"                                                                                                                                        
    pivotcol="product"                                                                                                                                          
    pivotdata="sales"  
    pivotdata_fmt="$#,###" 
    pivot_title="Pivot Analysis for XXX"
    pivot_format="light1"
    pivotcharts="yes"  
    pivot_chart_name="Profit Analysis Charts"
    chart_type="columnclustered" 
);                                                                                                                                  
 
/* dummy output to trigger the file creation */ 
data _null_;                                                                                                                                                                    
 file print;                                                                                                                                                            
 put "test";                                                                                                                                                              
run;                                                                                                                                                                  
                                                                                                                                                                 
ods tagsets.tableeditor close;
x "c:tempPivotChart.js";

Here is a sample of the PivotChart output.

PivotChart created with SAS ODS

Getting more help

To see a full list of tagsets.TableEditor options within SAS, including a list of PivotChart types that you can use, specify the DOC=”help” option.

filename d temp;  
ods tagsets.tableeditor file=d options( doc="help");                                                                                                                                                                          
ods tagsets.tableeditor close;

The complete reference documentation will appear in the SAS log.

See also

Using SAS® Output Delivery System (ODS) Markup to Generate Custom PivotTable and PivotChart Reports

Introduction to the TableEditor tagset

tags: Excel PivotChart, Excel PivotTable, ODS tagsets, Problem Solvers, SAS Programmers

The post Using SAS to add PivotTables to your Excel workbook appeared first on SAS Users.

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;
run;

The Log window shows the template codes:

15   proc template;
16       source Tagsets.ExcelXP;
define tagset Tagsets.ExcelXP;
   parent = Tagsets.ExcelBase;
end;
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";
run;

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

3. List all ODS template store path

ods path show;

Log:

Current ODS PATH list is:

1. SASUSER.TEMPLAT(UPDATE)
2. SASHELP.TMPLMST(READ)

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;

log:

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;
run;

Get output:

sasuser_templat2

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

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

 

6. Delete template

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

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.

log:

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):

sasuser_templat

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 ‘
http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl’;
%include excltags;

log:

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:

1. EXCLTAGS.TEMPLAT(UPDATE)
2. SASUSER.TEMPLAT(UPDATE)
3. SASHELP.TMPLMST(READ)

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
                    options(embedded_titles=’yes’
                            embedded_footnotes=’yes’
                            print_header=’&C&A&RPage &P of &N’
                            print_footer=’&RPrinted &D at &T’
                            autofilter=’2′);

%macro doit;

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

data _null_;
    set class end=eof;
    II=left(put(_n_,2.));
    call symputx(‘sex’||II,compress(sex));
    if eof then call symputx(‘total’,II);
run;

%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'];
    run;

%end;

%mend doit;

%doit

ods tagsets.ExcelXP close;

This .XML file can be opened by Office Excel:

SAS_ExcelXP

9. Get Help

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