I plan to dice and slice my datasets with different groupings in an Excel Pivot table. I have a huge SAS Dataset (64K + rows) that cannot be copied into Excel and then create a pivot table out of it.
For this very reason of overcoming the 64K rows limitation, I need to somehow import the SAS Dataset directly into a pivot table.
You can open ("external") data directly into a PivotTable without copying it to Excel. Any data that is accessible via ODBC or an OLEDB data provider (which includes SAS) can be opened into a PivotTable in Excel. Unfortunately, the Excel 2003 UI only lets you select ODBC data sources. You'll have to write VBA code to open a PivotTable in Excel 2003 using an OLEDB provider. The Excel 2007 UI lets you select ODBC or OLEDB providers.
For OLEDB, use the SAS IOM Data Provider if the data is on a SAS server; use the SAS Local Data Provider if data is on the local machine or accessed via UNC path. If you need help with the SAS data providers, see the SAS OLEDB Data Providers Cookbook in the OnlineDocs.
Note: The SAS Add-In for Microsoft Office handles all this for you -- just select your data and it opens it in the PivotTable.
Yes, Excel pulls the data to the client to do its aggregations for the PivotTable. I also wondered whether the 64k limit would still remain, so I tried it out. I opened a 1 million observation SAS data set into an Excel 2003 PivotTable without a problem (using Excel only -- no SAS Addin). So, it is evidently in memory, and avoids the 64k limit.
Of course, the Excel PivotTable performance is proportional to the size of the relational data. If performance becomes an issue, switching to an OLAP cube (which is pre-aggregated) avoids having Excel pull all the data and performing the aggregations.
The SAS Add-In for Microsoft Office is usually in a BI bundle.
Here is a sample VBA macro that creates an Excel PivotTable using a SAS cube, server data set, or local data set. It requires the SAS OLEDB data providers. And of course, you'd need to update the connection strings for your environment.
Dim pc As PivotCache
Dim pt As PivotTable
Dim connectionString As String
Dim commandType As XlCmdType
Dim commandText As String
Dim version As XlPivotTableVersionList
'Change these values to customize your PivotTable
Const dataType As Integer = 1 'Use OLAP cube (0), server data set (1), or local data set (2)
version = xlPivotTableVersion10 'Version of the PivotTable to create
'Add a PivotCache (for Excel2007 PivotTables, use the new Create method instead of Add
If version = xlPivotTableVersion12 Then
Set pc = ActiveWorkbook.PivotCaches.Create(xlExternal)
Set pc = ActiveWorkbook.PivotCaches.Add(xlExternal)
If dataType = 0 Then 'OLAP
connectionString = "OLEDB;Provider=SAS OLAP Data Provider 9.2;User ID=olaptst1;Password=*****;Data Source=bciba09;Location=bciba09;SAS Machine DNS Name=bciba09;SAS Port=5451;SAS Protocol=2;SAS Server Type=2;"
commandType = xlCmdCube
commandText = "PRDMDDB" 'The name of the cube to open
ElseIf dataType = 1 Then 'server relational
connectionString = "OLEDB;Provider=SAS.IOMProvider; User ID=sasdemo; Password=****; SAS Machine DNS Name=d14217.na.sas.com; SAS Protocol=2; SAS Port=8591;"
commandType = xlCmdTable
commandText = "SASHELP.CLASS" 'The name of the IOM dataset to open
connectionString = "OLEDB;Provider=sas.LocalProvider.9.2;Data Source=c:\program files\sas\enterpriseguide\4.3\sample\data;Mode=Read|Share Deny None"
commandType = xlCmdTable
commandText = "class" 'The name of the local dataset to open
pc.Connection = connectionString 'OLEDB connection string to the server
pc.commandType = commandType
pc.commandText = commandText
Set pt = pc.CreatePivotTable(TableDestination:=ActiveCell, DefaultVersion:=version)
Another option that is relatively easy compared to writing VBA code is to export the SAS data to an Access database then use the Access table as a source for the pivot table. I have Access databases that have hundreds of thousands of rows that work really well as sources for pivot tables.