Date:         Thu, 2 Jun 2005 06:55:36 -0600
Reply-To:     Alan Churchill <SASL001@SAVIAN.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Alan Churchill <SASL001@SAVIAN.NET>
Subject:      Re: SASTip 133 - Create a pivot table from SAS
Comments: To: Phil Mason <woodstreet@GMAIL.COM>
In-Reply-To:  <429ec80c.3764839d.1099.ffffaa29@mx.gmail.com>
Content-Type: text/plain; charset="us-ascii"

Phil,

Take a look at Visual Studio Tools for Office. It allows you to fully use
the Office object model and should provide a cleaner and more extensive
programming model:

http://msdn.microsoft.com/vstudio/office/officetools.aspx


Thanks,
Alan

Savian
"Bridging SAS and Microsoft Technologies"

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Phil
Mason
Sent: Thursday, June 02, 2005 2:49 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SASTip 133 - Create a pivot table from SAS

Daily Tip 133
02/06/2005 08:24:00

Create a pivot table from SAS


A colleague and I were looking at the best way to automatically create a
pivot table in EXCEL automatically from SAS. We considered solutions such as
ODS with MSO XML directives, straight XML, DDE, and so on - but these were
all very complex. He finally came up with the following simple method.

We use a SAS program to create a spreadsheet and then call a Visual Basic
Script. The Visual Basic Script does the following:

*       open the spreadsheet

*       add a new sheet for pivot table

*       create a pivot table using wizard

*       set the fields to be used in the table

The SAS program could be extended to make a macro which creates the VBS
file. This could then make it parameter driven to work for all data.


SAS Program


* create EXCEL spreadsheet ;

proc export data=sashelp.class

  outfile="c:\sas\class.xls"

  dbms=excel;

quit;

* call VB script to make the pivot table ;

data _null_;

  x 'c:\sas\pivot.vbs';

run;


VB Script Program


Set XL = CreateObject("Excel.Application")

XL.Visible=True

XL.Workbooks.Open "c:\sas\class.xls"

Xllastcell= xl.cells.specialcells(11).address

XL.Sheets.Add.name = "PivotTable"

xldata="class"

XL.Sheets(xldata).select

XL.ActiveSheet.PivotTableWizard SourceType=xlDatabase,XL.Range("A1" & ":" &
xllastcell),"Pivottable!R1C1",xldata

XL.ActiveSheet.PivotTables(xldata).PivotFields("Name").Orientation = 1

XL.ActiveSheet.PivotTables(xldata).PivotFields("Age").Orientation = 1

XL.ActiveSheet.PivotTables(xldata).PivotFields("Sex").Orientation = 1

XL.ActiveSheet.PivotTables(xldata).PivotFields("Height").Orientation = 4

XL.ActiveWorkbook.ShowPivotTableFieldList = False



Thanks to Chris Brooks, National Statistics, UK for this tip

Tested under SAS 9.1.3 & Windows XP Professional

Wood Street Consultants Ltd.
tips@woodstreet.org.uk

www.woodstreet.org.uk <http://www.woodstreet.org.uk/>