Using SAS to add PivotTables to your Excel workbook

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:\temp\temp.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:\temp\PivotTable.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:\temp\PivotTable.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:\temp\PivotChart.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:\temp\PivotChart.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

Post a Comment

SAS Global Forum Session Madness

MarchMadnessIt’s March and everyone is enjoying the madness, at least in the US. We are watching 68 college basketball teams compete for the title of NCAA Champion.

Those of us watching get involved by selecting a bracket, that is, picking who will win each game. Some pick their alma mater. Some choose a team because of the mascot. I just ask my kids! Basically, unless you use SAS analytics, the bracket selection process can be a guessing game.

With over 500 sessions and activities at SAS Global Forum 2015, you may feel like it’s a guessing game too. We have worked to make sure you don’t need help from your kids to build a personalized, winning schedule for the event this year. Filters on topics, skill level, and even job role along with keyword search functionality have made finding the right sessions for you easier than ever.

This year the conference team has set up sample session agendas to make it even easier to get started. These sample agendas are in various categories such as programming basics, biostatistics, and even some for those statisticians who are just getting started.

There is no guessing game involved in creating your own personalized journey. Get started today so you won’t miss a minute of the action during the event. Comment about your personalized journey here so others may benefit from your picks.

See you at SAS Global Forum and good luck with those NCAA picks!

Post a Comment

How to reverse graph axes in SAS Visual Analytics

It may never crossed your mind, but there are real-life cases when reversing graph axes make sense. For example, when Y-axis represents a depth under the sea level or depth of oil well drilling, it makes perfect sense to have zero on top and positive numbers increasing from top down. Or, when X-axis, represents time elapsed from some events in the past before some anchor event occurred, it make sense to have zero time on the right and positive numbers increasing going to the left.

This very useful feature of reversing axes in graphs is available in SAS Visual Analytics.  It’s a process that involves creating a custom graph template and then applying that template to your data in SAS Visual Analytics Report Designer. Here’s how in two quick steps: Read More »

Post a Comment

We heard you! SAS Global Forum 2015 is better than ever.

Each year, SAS Global Forum conference organizers take your responses to surveys and pore over the suggestions and feedback you provide. Content and content delivery are bigger and better than ever at SAS Global Forum 2015 because of your feedback.

Often, as good minds think alike, there will be trends and grouping of comments that really help us to refine the content we seek and the means by which we deliver it.  Below is a high-level list of some of these refinements that center around your remarks on content and content delivery:

  • Offering a few sessions during lunch where lunch is provided. We heard you—some would rather soak up extra content than sit at a table to eat. We have tried to offer both.
  • More recorded material. The fact is that we cannot be in two places at once. With nearly 600 presentations, we heard you and have increased our recording capability so you can catch up on missed sessions at your convenience.
  • A keynote presentation streamed from the SAS Executive Conference. This addition is in response to the level of interest in the great content being presented at the Executive Conference. We plan to increase this integration in the future—we heard you!
  • More hands-on experiences. We heard you—more hands on!
  • Quick Tips in one room with a new room design and a new look and feel.
  • A new session type – Table Talks – roundtable discussions for a more individual experience and interaction with the speaker
  • The Quad has the merchandise store and various “quadrants” to help align program areas (Learn, Network, Play, Socialize). We heard you, the Quad is one of the greatest draws to our attendees and we have worked hard to make this experience even better!
  • Improved scheduler…!!! We knew there were areas for improvement and were grateful for the many suggestions by our attendees as to how to make it better.

Remember, this is your conference!  Built and directed by SAS Users who need feedback from all of you to improve this already great conference. Come and check out the new features based upon your survey feedback!

Post a Comment

Descriptive statistics—the more, the merrier in SAS Visual Analytics

This blog is not about the original movie The More the Merrier (1943) or its remake, Walk, Don’t Run (1966), which I’ve actually seen a couple of times. It’s actually about the wide variety of descriptive statistics available in SAS Visual Analytics—and when you want to examine the characteristics of numeric data in your report—the more, the merrier. Read More »

Post a Comment

SAS FULLSTIMER—turn it on!

SAS FULLSTIMER is a SAS system option that takes operating system information that is being collected by SAS process runs and writes that information to the SAS log. Using it can add up to 10 lines additional lines to your SAS log for each SAS step in your SAS log—so why would I recommend turning it on? Read More »

Post a Comment

Relationships are easy – for SAS objects!

Somebody once said that relationships are not easy. Well now they are—in SAS 9.4, at least!

SAS objects have inherent relationships and dependencies, and a change to one element can often have an impact on one or many related objects. For example, for SAS Visual Analytics reports to work as designed, they need to have access to the data that was used to create them. However, when looking at a report, it is often difficult to determine which data table is the source, what columns are used in the report, or what LASR library the data uses? Enter the batch relationship reporting tools. Read More »

Post a Comment

SAS Global Forum 2015: three exciting changes that make it the best ever

This year I am expecting SAS Global Forum to be one of the biggest and best ever!

The SAS Global Users Group makes changes and enhancements each year at SAS Global Forum based on the feedback we receive from SAS users. And I am pleased to be the first to say “We heard you!” and to have a role in making some of them happen. Here are just a few things I am excited to share with you for this year’s conference in Dallas from April 26-29. Read More »

Post a Comment

SAS Visual Analytics: creating a custom sort order

Does your week start on Sunday or Monday? Do you have a promotional product you would like to see at the top of your list? Do you have a particular order in which you like to view your regions?

Custom sorting is now possible with SAS Visual Analytics 7.1. The ability to specify a custom sort order is available for category data items only. This includes any calculated data items that are categories and custom categories. Read More »

Post a Comment

Configuring storage for SAS--additional guidelines

My Performance Validation team in SAS R&D is constantly working with our partners to test how their storage arrays work with SAS.  In late 2014, we finalized several papers that discuss how a mixed analytics workload performs on several storage arrays.  While doing this testing, we also listed lessons-learned in the tuning guidelines of each paper.

Please review the papers listed below:

These papers, along with lots of other papers for other storage, can be found in Usage Note 53874: Troubleshooting system performance problems: I/O subsystem and storage papers.  Please bookmark this SAS Usage note as we update this list of papers regularly.

Let me know if you have questions about these papers or if there are other new storage systems that you would like SAS to test.

Post a Comment