/* Pivot_Demo.sas */
/* This is a demonstration of a technique to generate Excel pivot tables from SAS using a technique developed
   by Chris Brooks and Phil Mason and presented in a SUGI 31 paper (035-31) called "Sur La Table:
   Creating Microsoft Excel PivotTables in a Jiffy from SASŪ Data" by Ted Conway who extended the technique
   by employing a macro call to automate the VBS file creation. 

   Used by permission of the author, Ted Conway. 

   Paper is available at http://www2.sas.com/proceedings/sugi31/035-31.pdf */

/* You will require a c:/temp/ directory, and the sashelp.prdsal3 example dataset installed. */
 

/* This is the macro code that writes the VBS script based on the parameters passed below. */

%macro pivot(type, data=, var=, name=, orientation=, stat=, format=);
%if &type=workbook %then %do;
%global gblworkbook;
%let gblworkbook=&name;
data _null_;
set work.prdsal3_out;
file "c:\temp\RawData_prd.txt";
if _n_=1 then put "%sysfunc(translate(&var,'|',' '))";;
put %scan(&var,1) (%substr(&var,%index(&var,%str( )))) (+(-1) '|');
data _null_;
file "c:\temp\prdsal3.vbs";
put 'Set XL = CreateObject("Excel.Application")' / 'XL.Visible=True' /
'XL.Workbooks.OpenText "C:\temp\RawData_prd", 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_prd"").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\prdsal3.vbs';
%end;
%mend;

/* First, summarize the data using a simple table to generate one row with a total for each of the unique
   combinations of attributes of interest. */

proc tabulate FORMCHAR="|           " data=sashelp.prdsal3 out=work.prdsal3_out order=formatted missing;
class COUNTRY STATE PRODTYPE PRODUCT 
		YEAR QUARTER;
var ACTUAL;
tables COUNTRY*STATE*PRODTYPE*PRODUCT, 
		YEAR*QUARTER*ACTUAL;
title1 "Table 1: Actual Product Sales by Year and Quarter";
run;


/* Drop the SAS-specific _TYPE_ _PAGE_ _TABLE_ columns which won't be used. */

data work.prdsal3_out;
	set work.prdsal3_out;
	drop _TYPE_ _PAGE_ _TABLE_;
run;

/* The pivot table is set up and passes parameters to the macro code that writes
   the VBS script. */

%pivot(workbook, name=c:\temp\prdsal3_pvt.xls, data=work.prdsal3_out, 
			var=COUNTRY STATE PRODTYPE PRODUCT YEAR QUARTER ACTUAL_Sum
);

%pivot(worksheet, name=prdsal3_all);
%pivot(field, name=COUNTRY, Orientation=Page);
%pivot(field, name=STATE, Orientation=Row);
%pivot(field, name=PRODTYPE, Orientation=Row);
%pivot(field, name=PRODUCT, Orientation=Row);
%pivot(field, name=YEAR, Orientation=Column);
%pivot(field, name=QUARTER, Orientation=Column);
%pivot(field, name=ACTUAL_Sum, Orientation=Data, Stat=Sum, Format="$#,##0.00");
%pivot(resize);

%pivot(worksheet, name=prdsal3_country);
%pivot(field, name=COUNTRY, Orientation=Row);
%pivot(field, name=PRODTYPE, Orientation=Row);
%pivot(field, name=PRODUCT, Orientation=Row);
%pivot(field, name=YEAR, Orientation=Column);
%pivot(field, name=QUARTER, Orientation=Column);
%pivot(field, name=ACTUAL_Sum, Orientation=Data, Stat=Sum, Format="$#,##0.00");
%pivot(resize);

%pivot(worksheet, name=prdsal3_prod_type);
%pivot(field, name=COUNTRY, Orientation=Page);
%pivot(field, name=STATE, Orientation=Row);
%pivot(field, name=PRODTYPE, Orientation=Row);
%pivot(field, name=YEAR, Orientation=Column);
%pivot(field, name=QUARTER, Orientation=Column);
%pivot(field, name=ACTUAL_Sum, Orientation=Data, Stat=Sum, Format="$#,##0.00");
%pivot(resize);

%pivot(create);

run;