SAS Global Forum Hands-On Workshops
Here is my first attempt at live blogging on my new iPad and my first effort with the WordPress app.
Peter Eberhardt doing a Hands-On workshop at SAS Global Forum. It’s standing room only well it would be standing room only if they let you stand. It’s actually full & they are going to turn people away. If the turn you away, don’t pout, there are plenty of other awesome sessions.
The workshop is on doing the perfect pivot table with SAS
Starts by explaining what a PivotTable is & how do one in Excel
To do in SAS only need base SAS
Download the TableEditor tagset from the SAS website.
Link in Eberhard’s paper
TableEditor tagset not included in the SAS distribution
It’s a file with a PROC TEMPLATE
According to Eberhardt you don’t *have* to know anything other than it exists, but you can modify it if you want, for example to change the color scheme
=================
Start your program with
ODS TAGSETS.TABLEEDITOR FILE= “filename.html”
options( auto_excel = “yes” pivotrow =”some_name” pivotcol = “othername”
Creates an html file
auto_excel = yes —
====
Back to that later, I guess. Looking at the tableeditor.sas file.
Lots of proc template code.
===================
Okay, back to our ODS statement
options(
button__text = “”
auto_excel = “yes”
pivotrow = “some_name”
pivotcol = “other_name”
pivotdata = “other_name2”
pivotpage = “some_other_name”
=================
Don’t worry about the ActiveX warning that comes up. You also get told there may be data in the other worksheets and you might be deleting it. Just say it’s okay.
==============================================
Summary variable – sum statistics is selected by default
pivotdata_stats = “sum, average”
================
if you want a different column for each statistic, you can match them up like this,
pivotdata = “some_name, othername”
pivotdata_stats = “sum,average”
==========
The auto_excel = yes puts a button on your html output file which will automatically start Excel when you click on it
=============
Not sure the pivot table tagset is something I would use personally but it is kind of cool
Peter does not know if it would work with openoffice / libreoffice if you clicked on the auto_excel button. Well, that sounds like a challenge (-:
==================
Lots of javascript code involved in creating the html page. Not sure if I would use this but it is interesting to read to see how it is done
==================
Creating multiple pivot tables – I remember working at a company years ago where we did a zillion of these by hand , well automatically more or less using Excel, but we did do them one at a time. We wrote some clunky VBA macros.
==============
pivot_series = “yes” <--- gives you the multiple sheets pivotrow = "Name1 | Name1 | Name1" pivotcol = "Something | Something| Something" pivotdata = "Variable | Variable | Variable " pivotdata_stats = "sum | average | minimum" This will give you three pivot tables, one each with the sum, average and minimum of the variable over whatever the something column and Name1 rows are ============== Eberhardt says list of options for table editor tagset is in his paper ==== Big advantage of using tableeditor tagset is you can do the code once, automate the task and have it run every day, week or whatever. Again, I don't know if I would use this personally on the projects I'm working on now but I certainly can see the usefulness of it ===== Bottom line - if you have SAS & you need to do a lot of pivot tables in Excel particularly if you have to do the same ones repeatedly, just as the data change, get Eberhardt's paper in the SAS Global Forum proceedings. You can also creat pivot charts