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);

In statistics, the Jarque-Bera test is a goodness-of-fit measure of departure from normality, based on the sample kurtosis and skewness. The test statisticJB is defined as

where n is the number of observations (or degrees of freedom in general); S is the sample skewness, K is the sample kurtosis.

It is possible to download excel macro :

Jarque-Bera_test.txt (2.76 kb)

Descriptive statistics:

Measures of Skewness and Kurtosis:

Skewness is a measure of symmetry, or more precisely, the lack of symmetry. A distribution, or data set, is symmetric if it looks the same to the left and right of the center point.

Kurtosis is a measure of the "peakedness" of the probability distribution of a real-valued random variable. Higher kurtosis means more of the variance is due to infrequent extreme deviations, as opposed to frequent modestly-sized deviations.