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: