Creating good looking Excel Pivot Tables to hold your nicely summarized SAS data can be surprisingly labor intensive. This paper presents a technique and macro that combine Base SAS with Visual Basic Scripting to make it possible to fully automate the creation of custom formatted Excel Pivot Tables from SAS data sets.

Creating good looking Excel Pivot Tables from SAS"

proc export data=sashelp.class outfile= "c:\class.xls" dbms=excel; 
run;

%macro pivot(type, data=, var=, name=, orientation=, stat=, format=);
%if &type=workbook %then %do;
%global gblworkbook;
%let gblworkbook=&name;
data _null_;
set sashelp.class;
file "c:\temp\RawData.txt";
if _n_=1 then put "%sysfunc(translate(&var,'|',' '))";;
put %scan(&var,1) (%substr(&var,%index(&var,%str( )))) (+(-1) '|');
data _null_;
file "c:\temp\class.vbs";
put 'Set XL = CreateObject("Excel.Application")' / 'XL.Visible=True' /
'XL.Workbooks.OpenText

 "C:\temp\RawData.txt", 437, 1, 1, -4142, False, False, False, False, False, True, "|"';
%end;
%if &type=worksheet %then %do;
put "XL.Sheets.Add.name = ""&name""" /
"XL.ActiveSheet.PivotTableWizard SourceType=xlbase, xl.sheets(""RawData"").UsedRange,
 ""&name!R1C1"", ""pvttbl""";
%end;
%if &type=field %then %do;
%if &orientation=Data %then %do;
put "XL.ActiveSheet.PivotTables(""pvttbl"").AddDataField

 XL.ActiveSheet.PivotTables(""pvttbl"").PivotFields(""&name""), ""&Stat of &name"",
"
%if &stat=Sum %then "-4157";
%if &stat=Count %then "-4112";
%if &stat=Average %then "-4106";;
%end;
%else %do;
put "XL.ActiveSheet.PivotTables(""pvttbl"").PivotFields(""&name"").Orientation = "
%if &orientation=Page %then "3";
%if &orientation=Row %then "1";
%if &orientation=Column %then "2";
%end;;
%if &format^= %then
%if &stat^= %then
put "XL.ActiveSheet.PivotTables(""pvttbl"").PivotFields(""&stat of &name"").numberformat 
= " ""&format"";
%else
put "XL.ActiveSheet.PivotTables(""pvttbl"").PivotFields(""&name"").numberformat = " ""&format"";;
%end;
%if &type=resize %then
put "XL.ActiveSheet.Columns.AutoFit";;
%if &type=create %then %do;
put "XL.ActiveWorkbook.SaveAs ""&gblworkbook"", -4143";;
run;
x 'c:\temp\class.vbs';
%end;
%mend;

%pivot(workbook, name=c:\temp\class.xls, data=sashelp.class, var=Name Age Sex Height);
%pivot(worksheet, name=TestPivotTable3);
%pivot(field, name=Sex, Orientation=Column);
%pivot(field, name=Age, Orientation=Row, Format="00"); 
%pivot(field, name=Height, Orientation=Data, Stat=Sum, Format="#,##0.00"); 
%pivot(resize);
%pivot(worksheet, name=TestPivotTable2);
%pivot(field, name=Sex, Orientation=Row);
%pivot(field, name=Height, Orientation=Data, Stat=Sum, Format="#,##0.00"); 
%pivot(field, name=Height, Orientation=Data, Stat=Average, Format="#,##0.0"); 
%pivot(field, name=Height, Orientation=Data, Stat=Count, Format="#,##0"); 
%pivot(resize);
%pivot(worksheet, name=TestPivotTable1);
%pivot(field, name=Sex, Orientation=Page);
%pivot(field, name=Name, Orientation=Row);
%pivot(field, name=Height, Orientation=Data, Stat=Sum, Format="#,##0.00"); 
%pivot(resize);
%pivot(create);