Pivot Tables using SAS

Excel Pivot tables through SAS

Quick Tip

Objective: Hello readers. Pivot table in Excel is an important tool which is used heavily and which makes lot of people’s life much simpler in doing a quick analysis on small data.

It saves the effort to go into SAS create a dataset and start writing code for each scenario. The drag and drop is even more intuitive and easily customizable.

SO I thought of sharing this quick tip to get you started with creating pivot tables directly from your preexisting program which creates the report.

What is a Pivot Table?

I am not a pivot table expert and do not intend to explain Pivot tables here but just for the definition’s sake according to Wikipedia

“In data processing, a pivot table is a data summarization tool found in data visualization programs such as Excel. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data.”

Why use SAS to create one?

Let me tell you one scenario. In SAS reporting mostly we create a final dataset after data summarization which we export to Excel, format it and send to client. But what if your client knows a bit of Excel(which will be the case mostly) he will be happier to get a Pivot table in which he can do a bit of drag and drop to see other metrics for a quick check which you did not deliver(coz you were not supposed to).

Or many times you deliver a few Pivot tables showing different Metrics from the same data.

In both the cases if you automated SAS code creates various Pivots directly without you exporting the data and creating tables manually, all you just have to do is quick checks before sending it forward.

Yes yes I heard that…I know there are many ways to do the same thing. I just liked it, did that on one of my for  my boss and he was awed..so I thought maybe you could gain some extra points as well and there is almost no effort involved.

The code:

Ok enough talking, all you have to do is to use this code :

ods path(prepend) work.templat(update);

filename temp url "http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/tableeditor.tpl";

%include temp;

ods tagsets.Tableeditor file="%sysfunc(getoption(work))\temp.html"

options(pivotrow="age"

        pivotcol="sex"

          pivotdata="height,weight"

        pivotdata_stats="sum,mean");

proc print data=sashelp.class;

run;

ods tagsets.tableeditor close;

Explanation:

Yes, its that simple, and the code is self explanatory, just replace your variables in Pivotrow= pivotcol= etc., specify the statistics you want on pivotdata_stat= and you are good to go. you’ll get a ODS HTML output with a button. Just click it and the final data is pasted in an excel and a pivot generated.

The URL filename just downloads the tableeditor tagset from SAS website. You can donload and keep it in your local and reference it

Conclusion: This is a clean time saving method to create pivots through SAS. Just give it a try and let me know how did it go in the comments below.

I will delve into SAS and Excel Pivot interface details in a future post.

Will be back with some more SAS magic. Goodbye till then.

Saurabh Singh  Chauhan

(er.chauhansaurabh@gmail.com)         

Note: Comments and suggestions are always welcome.

Disclaimer :
SAS® and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc.in the USA and other countries. ® Indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective companies. 

The contents of this post are the works of the author(s)and do not necessarily represent the opinions,recommendations, or practices of any organization whatsoever.