Eberhardt PivotTables
Transcript of Eberhardt PivotTables
-
8/13/2019 Eberhardt PivotTables
1/111
Copyright 2012, Fernwood Consulting Group Inc. All rights reserved.
The Armchair Quarterback:Writing SAS Code for thePerfect Pivot (Table, That Is)
Peter EberhardtFernwood Consulting Group Inc.
Louanna Kong
SAS Software (Beijing) Co. Ltd., China
-
8/13/2019 Eberhardt PivotTables
2/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Agenda
Review Excel Pivot TablesData globalForum;
infile examples;
do until (timesUp);
input example 2.;
call ShowExcel(example);
call ShowSAS(example);
call youDo(example);
end;
run;
Impress your boss,get a raise,retire rich
-
8/13/2019 Eberhardt PivotTables
3/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Thanks to Chevell Parker
-
8/13/2019 Eberhardt PivotTables
4/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
What is a Pivot Table?
Spreadsheet term
Data summarization
Cross tab with more than two dimensions
Drag and drop
Scaled down version of OLAP
PivotTable is a trademarked term of Microsoft
-
8/13/2019 Eberhardt PivotTables
5/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
What is a Pivot Table?
Data table
-
8/13/2019 Eberhardt PivotTables
6/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
What is a Pivot Table?
Data data table
-
8/13/2019 Eberhardt PivotTables
7/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
What is a Pivot Table?
Wizard
-
8/13/2019 Eberhardt PivotTables
8/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
What is a Pivot Table?
-
8/13/2019 Eberhardt PivotTables
9/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
What is a Pivot Table?
-
8/13/2019 Eberhardt PivotTables
10/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
What is a Pivot Table?
-
8/13/2019 Eberhardt PivotTables
11/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
What is a Pivot Table?
Terminology PivotTable Field List
PivotTable Areas
Report Filter
Where clause Column Labels
Classification variable
Row Labels
Classification variable Summary Values
Statistic
-
8/13/2019 Eberhardt PivotTables
12/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
TableEditor Tagset
PivotTables from SAS Base SAS
ODS
TableEditor Tagset
Not included in SAS distribution See the paper to get the download location
-
8/13/2019 Eberhardt PivotTables
13/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
TableEditor Tagset
PivotTables from SAS Base SAS
ODS
TableEditor Tagset
Not included in SAS distribution See the paper to get the download location
t l t
-
8/13/2019 Eberhardt PivotTables
14/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
proc template;define tagset Tagsets.Tableeditor;
mvar sysver;define event initialize;do /if $options["BANNER_COLOR_EVEN"];
set $banner_even $options["BANNER_COLOR_EVEN" ];
else;unset $banner_even;
done;
do /if $options["BANNER_COLOR_ODD"];set $banner_odd $options["BANNER_COLOR_ODD" ];
else;unset $banner_odd;done;
do /if $options["FBANNER_COLOR_EVEN"];set $fbanner_even $options["FBANNER_COLOR_EVEN" ];
else;
unset $fbanner_even;done;
do /if $options["FBANNER_COLOR_ODD"];set $fbanner_odd $options["FBANNER_COLOR_ODD" ];
-
8/13/2019 Eberhardt PivotTables
15/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
else;unset $fbanner_odd;
done;
do /if $options["COL_COLOR_ODD"];set $col_odd $options["COL_COLOR_ODD" ];
else;unset $col_odd;
done;
do /if $options["COL_COLOR_EVEN"];set $col_even $options["COL_COLOR_EVEN"];
else;unset $col_even;
done;
do /if $options["HEADER_BCOLOR"];set $header_bcolor $options["HEADER_BCOLOR" ];
else;unset $header_bcolor;
done;
do /if cmp( $options["OVERRIDE"], "yes");set $override "yes";
-
8/13/2019 Eberhardt PivotTables
16/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
else;unset $override;
done;
do /if cmp( $options["FROZEN_HEADERS"], "yes");set $frozen_headers "yes";
else;unset $frozen_headers;
done;
do /if $options["FROZEN_ROWHEADERS"];set $frozen_rowheaders $options["FROZEN_ROWHEADERS" ];
else;unset $frozen_rowheaders;
done;
do /if $options["HIGHLIGHT_COLOR"];set $highlight_color $options["HIGHLIGHT_COLOR" ];
else;unset $highlight_color;
done;
do /if $options["GRIDLINES"];set $borders $options["GRIDLINES" ];
The Armchair Q arterback Writing SAS Code for the Perfect Pi ot (Table That Is)
-
8/13/2019 Eberhardt PivotTables
17/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
TableEditor Tagset
PivotTables from SAS* turn off the defaule listing destination;
ods tagsets.tableeditor
file="&resultsHome\example1.html"
options(
auto_excel = "yes" pivotrow = "product_line"
pivotcol = "quarter" pivotdata = "profit"
pivotpage = "year" quit="NO"
excel_save_file="&JavaResultsHome\example1.xlsx"
);
Title1 "Example 1 - Create the First Pivot Table";proc print data=sashelp.orsales;
run;
ods tagsets.tableeditor close;
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table That Is)
-
8/13/2019 Eberhardt PivotTables
18/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
TableEditor Tagset
PivotTables from SAS* turn off the defaule listing destination;
ods tagsets.tableeditorfile= &resultsHome\example1.htmloptions(
auto_excel = "yes" pivotrow = "product_line"
pivotcol = "quarter" pivotdata = "profit"
pivotpage = "year" quit="NO"
excel_save_file="&JavaResultsHome\example1.xlsx"
);
Title1 "Example 1 - Create the First Pivot Table";proc print data=sashelp.orsales;
run;
ods tagsets.tableeditor close;
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table That Is)
-
8/13/2019 Eberhardt PivotTables
19/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
TableEditor Tagset
PivotTables from SAS* turn off the defaule listing destination;
ods tagsets.tableeditor
file="&resultsHome\example1.html"
options(auto_excel = yes pivotrow = product_linepivotcol = quarter pivotdata = profitpivotpage = year quit= NOexcel_save_file= &JavaResultsHome\example1.xlsx);
Title1 "Example 1 - Create the First Pivot Table";proc print data=sashelp.orsales;
run;
ods tagsets.tableeditor close;
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table That Is)
-
8/13/2019 Eberhardt PivotTables
20/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
TableEditor Tagset
PivotTables from SAS* turn off the defaule listing destination;
ods tagsets.tableeditor
file="&resultsHome\example1.html"
options(
auto_excel = "yes" pivotrow = "product_line"
pivotcol = "quarter" pivotdata = "profit"
pivotpage = "year" quit="NO"
excel_save_file="&JavaResultsHome\example1.xlsx"
);
Title1 Example 1 - Create the First Pivot Table ;proc print data=sashelp.orsales;run;ods tagsets.tableeditor close;
-
8/13/2019 Eberhardt PivotTables
21/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
22/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
23/111Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
TableEditor Tagset
PivotTables from SAS ODS tagsets.tableEditor destination
Generates a web page with detail data
A button to start Excel is automatically added
The first example we bypassed the button with theauto_excel=YESoption
Generates java script to build the pivot table
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
24/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Exercise 1
Click on the Eberhardt146icon to start SAS
In SAS
File Open Program
You should be in Examplesfolder
Open tableEditor.sas
This will create a temporary tableEditor tagset Submit this code and check the log.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
25/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Q g ( , )
Exercise 1
Open exercise1.sas>>> create the pivot table just shown
button text will be Create PivotTableauto excel will be yespivot row will be product_linepivot columnwill be quarterpivot data will be profitpivot page will be yearquit will be NO
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
26/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
g ( , )
Exercise 1
Open exercise1.sasods tagsets.tableeditorfile= &resultsHome\example1.htmloptions(
button_text = Create PivotTableauto_excel = yespivotrow = product_linepivotcol = quarterpivotdata = profitpivotpage = yearexcel_save_file= &JavaResultsHome\example1.xlsxquit= NO);
-
8/13/2019 Eberhardt PivotTables
27/111
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
28/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Summary Variables
Change the summary statistic from sum to average Click on the Sum of Profit drop down
Click on Value Field setting
Select Average from the list
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
29/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Summary Variables
Add quantity to summary values area
-
8/13/2019 Eberhardt PivotTables
30/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
31/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Summary Variables
I want to see one column per year Drag the Summary values from the column area to the row area
-
8/13/2019 Eberhardt PivotTables
32/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
33/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Summary Variables
In SAS
ods tagsets.tableeditor
file="&resultsHome\example2.html"
options(button_text = "Excel"
pivotrow = "product_line"
pivotcol = "year"
pivotdata = profit,quantitypivotdata_stats = average,sum");
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
34/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 2
Open exercise2.sas
>>> create the pivot table just shown
pivot row will be product_linepivot column will be yearpivot datawill be profit,quantitypivot datastatistics will be sum,average
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
35/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 2
ods tagsets.tableeditorfile= &resultsHome\example2.htmloptions(
button_text = Excelpivotrow = product_linepivotcol = yearpivotdata = profit,quantitypivotdata_stats = sum,average);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
36/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Pivot Tables
Three pivotTables1. Minimum profit by quarter (columns) and product line (row)
2. Maximum profit by quarter (columns) and product line (row)
3. Average profit by quarter (columns) and product line (row)
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
37/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Pivot Tables
Repeat the process to create a pivotTable for eachsheet
-
8/13/2019 Eberhardt PivotTables
38/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
39/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Pivot Tables
In SASods tagsets.tableeditor
file="&resultsHome\example3.html"
options(
button_text = "Excel"
pivot_series="yes"pivotrow="Product_Line | Product_Line | Product_Line"
pivotcol=" Quarter | Quarter | Quarter"
pivotdata="Profit | Profit | Profit"
pivotdata_stats="Min | Max | Average"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
40/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Pivot Tables
In SASods tagsets.tableeditor
file="&resultsHome\example3.html"
options(
button_text = "Excel"
pivot_series= yespivotrow="Product_Line | Product_Line | Product_Line"
pivotcol=" Quarter | Quarter | Quarter"
pivotdata="Profit | Profit | Profit"
pivotdata_stats="Min | Max | Average"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
41/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Multiple Pivot Tables
In SASods tagsets.tableeditor
file="&resultsHome\example3.html"
options(
button_text = "Excel"
pivot_series="yes"pivotrow= Product_Line | Product_Line |Product_Linepivotcol= Quarter |Quarter | Quarterpivotdata= Profit |Profit | Profitpivotdata_stats= Min |Max |Average
);
-
8/13/2019 Eberhardt PivotTables
42/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
43/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 3
Open exercise3.sas
>>> create the pivot table just shown
pivot rowwill be product_linepivot columnwill be quarterpivot datawill be profit
first sheet statistic will be min, second sheet statistic will be max,
third sheet statistic will be Average
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
44/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 3
ods tagsets.tableeditorfile= &resultsHome\example3.htmloptions(
button_text = Excelpivot_series = yespivotrow = Product_Line | Product_Line | Product_Linepivotcol = Quarter | Quarter | Quarterpivotdata = Profit | Profit | Profitpivotdata_stats = Min | Max | Average
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
45/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Percent of Total
Statistic as percent of a total Percent of Row Total
Percent of Column Total
Percent of Grand Total
year as column area
product_category as row area
quantity as summary variable
Sum
Percent of row total
-
8/13/2019 Eberhardt PivotTables
46/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
47/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
48/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Percent of Total
In SASods tagsets.tableeditor
file="&resultsHome\example4.html"
options(
button_text = "Excel"
sheet_name = "Qty_By_Year"pivotrow = "product_category"
pivotcol = "year"
pivotdata = "quantity"
pivotdata_stats = "sum"
pivotcalc = "row"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
49/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Percent of Total
In SASods tagsets.tableeditor
file="&resultsHome\example4.html"
options(
button_text = "Excel"
sheet_name = "Qty_By_Year"pivotrow = "product_category"
pivotcol = "year"
pivotdata = "quantity"
pivotdata_stats = "sum"
pivotcalc = row);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
50/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Percent of Total
In SASods tagsets.tableeditor
file="&resultsHome\example4.html"
options(
button_text = "Excel"
sheet_name = Qty_By_Yearpivotrow = "product_category"
pivotcol = "year"
pivotdata = "quantity"
pivotdata_stats = "sum"
pivotcalc = "row"
);
-
8/13/2019 Eberhardt PivotTables
51/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
52/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 4
Open exercise4.sas>>> create the pivot table just shown
pivot rowwill be product_linepivot columnwill be quarterpivot datawill be profit
first sheet statistic will be min, second sheet statistic will be max,
third sheet statistic will be Average
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
53/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 4
ods tagsets.tableeditorfile= &resultsHome\example4.htmloptions(
button_text = Excelsheet_name = Qty_By_Yearpivotrow = product_categorypivotcol = yearpivotdata = quantitypivotdata_stats = sumpivotcalc = row);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
54/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Formatting Data
Excel offers a variety of formatting options Use the pivotTable interface
Value Field settings
Each data variable needs to be set separately
-
8/13/2019 Eberhardt PivotTables
55/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
56/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
57/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Formatting Data
Excel offers a variety of formatting options Custom format
[blue] #,###
Colour in square bracket (optional)
#,###
Insert commas, no decimal
Custom formats can have 4 sections separated by semi-colon (;)
1. Format for positive
2. Format for negative3. Format for zero
4. Format for text
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
58/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Formatting Data
In SASods tagsets.tableeditor
file="&resultsHome\example5.html"
options(
button_text="Excel"
pivotrow="product_line"pivotcol="year"
pivotdata="Profit,Quantity,salesTotal"
pivotdata_stats="Sum,Average,Max"
pivotdata_fmt="[blue] #,###~[red] $#,###.##~$#,###.##"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
59/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Formatting Data
In SASods tagsets.tableeditor
file="&resultsHome\example5.html"
options(
button_text="Excel"
pivotrow="product_line"pivotcol="year"
pivotdata= Profit,Quantity,salesTotalpivotdata_stats="Sum,Average,Max"
pivotdata_fmt="[blue] $#,### ~ [red] #,###.## ~ $#,###.##"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
60/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Formatting Data
In SASods tagsets.tableeditor
file="&resultsHome\example5.html"
options(
button_text="Excel"
pivotrow="product_line"pivotcol="year"
pivotdata="Profit,Quantity,salesTotal"
pivotdata_stats="Sum,Average,Max"
pivotdata_fmt= [blue] $#,### ~ [red] #,###.## ~ $#,###.##);
-
8/13/2019 Eberhardt PivotTables
61/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
62/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 5
Open exercise5.sas>>> create the pivot table just shown
pivotrow will be product linepivotcol will be yearpivotdata will be profit, quantity, salesTotalpivotdata_stats will be sum, average, maxpivotdata_fmt will be
SUM: bluewith dollar/commas,AVERAGE: redwith commas,MAX: defaultwith dollar/commas
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
63/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 5
ods tagsets.tableeditorfile= &resultsHome\example5.htmloptions(
button_text = ExcelPivotrow = product_linePivotcol = yearPivotdata = Profit, Quantity, salesTotalpivotdata_stats = Sum, Average, Maxpivotdata_fmt = [blue] $#,### ~ [red] #,###.## ~ $#,###.##
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
64/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Adding PivotTable to the Data Sheet
Specify the location on the data sheet in the wizard
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
65/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Adding PivotTable to the Data Sheet
In SASods tagsets.tableeditor
file="&resultsHome\example7.html"
options(
button_text = "Excel"
sheet_name = "Pivot With Data"
excel_autofilter = "yes"
ptdest_range= "g1"
pivotrow = "product_category"
pivotcol = "year"
pivotdata ="profit"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
66/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Adding PivotTable to the Data Sheet
In SASods tagsets.tableeditor
file="&resultsHome\example7.html"
options(
button_text = "Excel"
sheet_name = "Pivot With Data"
excel_autofilter = "yes"
ptdest_range= g1pivotrow = "product_category"
pivotcol = "year"
pivotdata ="profit"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
67/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Adding PivotTable to the Data Sheet
In SASods tagsets.tableeditor
file="&resultsHome\example7.html"
options(
button_text = "Excel"
sheet_name = "Pivot With Data"
excel_autofilter = yesptdest_range= "g1"
pivotrow = "product_category"
pivotcol = "year"
pivotdata ="profit"
);
-
8/13/2019 Eberhardt PivotTables
68/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
69/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
70/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 7
Open exercise7.sas>>> create the pivot table just shown
sheet_namewill be Qty_By_Yearpivotrow will be product_categorypivotcol will be yearpivotdata will be profitptdest will be G1
pivot table and data are on same sheet
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
71/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 7
ods tagsets.tableeditor
file="&resultsHome\example7.html"
options(
button_text = "Excel"
sheet_name = "Pivot With Data"
excel_autofilter = "yes"ptdest_range = "g1"
pivotrow = "product_category"
pivotcol = "year"
pivotdata = "profit"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
72/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Adding Sheets to an Existing Workbook
Excel offers numerous ways to add a sheet to an existingworkbook
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
73/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Adding Sheets to an Existing Workbook
In SASods tagsets.tableeditor
file="&resultsHome\example8.html"
options(
button_text = "Excel"
update_target="&JavaResultsHome.\example7.xlsx"
sheet_name="Sales"
pivotrow="product_line"
pivotcol="year"
pivotdata="profit"
pivot_format="medium5"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
74/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Adding Sheets to an Existing Workbook
In SAS \ods tagsets.tableeditor
file="&resultsHome\example8.html"
options(
button_text = "Excel"
update_target= &JavaResultsHome.\ example7.xlsxsheet_name="Sales"
pivotrow="product_line"
pivotcol="year"
pivotdata="profit"
pivot_format="medium5"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
75/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Adding Sheets to an Existing Workbook
In SAS \ods tagsets.tableeditor
file="&resultsHome\example8.html"
options(
button_text = "Excel"
update_target="&JavaResultsHome.\example7.xlsx"
sheet_name="Sales"
pivotrow="product_line"
pivotcol="year"
pivotdata="profit"
pivot_format= medium5);
-
8/13/2019 Eberhardt PivotTables
76/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
77/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 8
Open exercise8.sas>>> create the pivot table just shown
sheet_namewill be Salespivotrow will be product linepivotcol will be yearpivotdata
will be Profitpivot_formatwill be medium5update_targetwill be &JavaResultsHome.\example7.xlsx
--> update_target is an existing workbook that will updated
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
78/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 8
ods tagsets.tableeditorfile= &resultsHome\example8.htmloptions(
button_text = Excelupdate_target = &JavaResultsHome.\example7.xlsxsheet_name = Salespivotrow = product_linepivotcol = yearpivotdata = profitpivot_format = medium5);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
79/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Updating a Range in an Existing Workbook
SAS Access for PC File Formats
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
80/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Updating a Range in an Existing Workbook
ods tagsets.tableeditor file= &resultsHome\example9.htmloptions(
button_text = Excelupdate_target= &javaDataHome\example9.xlsxupdate_sheet= Salesupdate_range= 1,7);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
81/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Updating a Range in an Existing Workbook
ods tagsets.tableeditor file= &resultsHome\example9.htmloptions(
button_text = Excelupdate_target= &javaDataHome\example9.xlsxupdate_sheet= Salesupdate_range= 1,7);
-
8/13/2019 Eberhardt PivotTables
82/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
83/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
84/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 9
Open exercise9.sas>>> create the pivot table just shownbutton_text will be"Excelupdate_target will be&javaDataHome\example9.xlsxupdate_sheet will beSalesupdate_range will be row 1 column 7
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
-
8/13/2019 Eberhardt PivotTables
85/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 9
ods tagsets.tableeditorfile= &resultsHome\example9.htmloptions(
button_text = Excelupdate_target= &javaDataHome\example9.xlsxupdate_sheet= Salesupdate_range= 1,7);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
C f
-
8/13/2019 Eberhardt PivotTables
86/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotTables from Existing Workbook
Data already loaded in Excel from another source
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
C i i T bl f E i i W kb k
-
8/13/2019 Eberhardt PivotTables
87/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotTables from Existing Workbook
ods tagsets.tableeditor file="&resultsHome\example10.html"
options(
update_target ="&javaDataHome\example10.xlsx"
sheet_name ="Profit, Quantity, Sales"
pivotrow ="Product_line | Product_line | Product_line"
pivotdata ="Profit | Quantity | Sales"auto_excel = "yes"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
C ti i tT bl f E i ti W kb k
-
8/13/2019 Eberhardt PivotTables
88/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotTables from Existing Workbook
ods tagsets.tableeditor file="&resultsHome\example10.html"
options(
update_target ="&javaDataHome\example10.xlsx"
sheet_name = Profit, Quantity, Salespivotrow ="Product_line | Product_line | Product_line"
pivotdata ="Profit | Quantity | Sales"auto_excel = "yes"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
C ti i tT bl f E i ti W kb k
-
8/13/2019 Eberhardt PivotTables
89/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotTables from Existing Workbook
ods tagsets.tableeditor file="&resultsHome\example10.html"
options(
update_target ="&javaDataHome\example10.xlsx"
sheet_name ="Profit, Quantity, Sales"
pivotrow = Product_line | Product_line | Product_linepivotdata = Profit | Quantity | Salesauto_excel = "yes"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
C ti i tT bl f E i ti W kb k
-
8/13/2019 Eberhardt PivotTables
90/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotTables from Existing Workbook
ods tagsets.tableeditor file="&resultsHome\example10.html"
options(
update_target ="&javaDataHome\example10.xlsx"
sheet_name ="Profit, Quantity, Sales"
pivotrow ="Product_line | Product_line | Product_line"
pivotdata ="Profit | Quantity | Sales"auto_excel = yes);
-
8/13/2019 Eberhardt PivotTables
91/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
92/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
E i 10
-
8/13/2019 Eberhardt PivotTables
93/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 10
Open exercise10.sas
>>> create the pivot table just shown
update_target will be "&javaDataHome\example10.xlsx"
sheet_name will be Profit, Quantity, Sales
pivotrow will be "Product_line |Product_line | Product_line"
pivotdata will be "Profit | Quantity | Sales"
auto_excel will be yes
Note the use of the DATA _NULL_ report to generate a dummy web page
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
E i 10
-
8/13/2019 Eberhardt PivotTables
94/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 10
ods tagsets.tableeditor file= &resultsHome\example10.htmloptions(
update_target= &javaDataHome\example10.xlsxsheet_name= Profit, Quantity, Salespivotrow= Product_line | Product_line | Product_linepivotcol= year | year | yearpivotdata= Profit | Quantity | Salesauto_excel = yes);
Title1 Example 10 - PivotTables from an Existing Workbook ;data _null_;file print;put Create Pivot Tables ;run;ods tagsets.tableeditor close;
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
C ti i tCh t
-
8/13/2019 Eberhardt PivotTables
95/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotCharts
Wizardinsert pivotChart
Creates pivotTable AND pivotChart
Changes to the table are immediately reflected in chart
-
8/13/2019 Eberhardt PivotTables
96/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
97/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
98/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
99/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Creating pivotCharts
-
8/13/2019 Eberhardt PivotTables
100/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotCharts
In SAS
ods tagsets.tableeditor file="&resultsHome\example11.html"
options(
button_text = "Excel"
pivotrow ="product_line"
pivotcol = "year"
pivotdata = "profit"
pivotdata_fmt= "#,###"
pivotcharts = yeschart_type = conecol
);
-
8/13/2019 Eberhardt PivotTables
101/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
-
8/13/2019 Eberhardt PivotTables
102/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Exercise 11
-
8/13/2019 Eberhardt PivotTables
103/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 11
Open exercise11.sas
>>> create the pivot table just shown
pivotrow will be product_linepivotcol will be yearpivotdata will be profitpivotdata_fmt will be #,###pivotcharts will be YESchart_type will be conecol
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Exercise 11
-
8/13/2019 Eberhardt PivotTables
104/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Exercise 11
ods tagsets.tableeditor file= &resultsHome\example11.htmloptions(
button_text = Excelpivotrow= product_linepivotcol= yearpivotdata= profitpivotdata_fmt= #,###pivotcharts= yeschart_type= conecol
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Creating pivotCharts
-
8/13/2019 Eberhardt PivotTables
105/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotCharts
Adding notations to the chart
options(
pivotcol="year"
pivotdata="profit"
pivotdata_fmt="#,###"
pivotcharts="yes"chart_type="cylindercol"
chart_title="Profit Analysis"
chart_yaxes_title="Profit"
chart_xaxes_title="Product Line"
chart_xaxes_orientation="45"
chart_yaxes_numberformat="currency"
);
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Creating pivotCharts
-
8/13/2019 Eberhardt PivotTables
106/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Creating pivotCharts
Adding notations to the chart
options(
pivotcol="year"
pivotdata="profit"
pivotdata_fmt="#,###"
pivotcharts="yes"chart_type="cylindercol"
chart_title= Profit Analysischart_yaxes_title= Profitchart_xaxes_title= Product Linechart_xaxes_orientation= 45chart_yaxes_numberformat=" $ #,###.##");
-
8/13/2019 Eberhardt PivotTables
107/111
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Review
-
8/13/2019 Eberhardt PivotTables
108/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Review
TableEditor tagset
Easy to create PivotTables and PivotCharts
Numerous options to control virtually every part of thePivotTable and PivotChart
See paper for the options
Can generate data as part of the process
Can use data in existing workbooks
The Armchair Quarterback: Writing SAS Code for the Perfect Pivot (Table, That Is)
Thanks
-
8/13/2019 Eberhardt PivotTables
109/111
Copyright 2013 Peter Eberhardt, Fernwood Consulting Group Inc. All rights Reserved.
Thanks
To you for attending
Maribeth and Ann for asking us to speak
Chevell Parker for the sharing his knowledge
-
8/13/2019 Eberhardt PivotTables
110/111
Peter [email protected]
Louanna Kong
mailto:[email protected]:[email protected] -
8/13/2019 Eberhardt PivotTables
111/111