LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (June 2005, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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/>


Back to: Top of message | Previous page | Main SAS-L page