topic Re: Importing a SAS Dataset directly into an EXCEL Pivot table in Microsoft Integration with SAS

Importing a SAS Dataset directly into an EXCEL Pivot table

SMS — Mon, 07 Jun 2010 13:48:53 GMT

Hi All,
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.

Can you please suggest me a way if there is any ?

Thank you

Re: Importing a SAS Dataset directly into an EXCEL Pivot table

CaseySmith — Mon, 07 Jun 2010 14:39:36 GMT

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.

Re: Importing a SAS Dataset directly into an EXCEL Pivot table

SMS — Mon, 07 Jun 2010 14:50:43 GMT

Thank you Casey for the reply.

I am not very proficient at VBA, so i will see if any other forum member (SAS expert) has written anything similar.

As for the SAS Add in , is it a part of a certain package ? Does any detail in my license info tell me if my company has it ?

Thanks

Re: Importing a SAS Dataset directly into an EXCEL Pivot table

Bill — Mon, 07 Jun 2010 14:58:55 GMT

I know next to nothing about excel, but ...

Doesn't a pivot table need all of the raw data to operate its slicing and dicing routines? If so, the 64k limit remains (unless you have the SAS Addin).

Re: Importing a SAS Dataset directly into an EXCEL Pivot table

CaseySmith — Mon, 07 Jun 2010 15:20:30 GMT

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.

Re: Importing a SAS Dataset directly into an EXCEL Pivot table

CaseySmith — Mon, 07 Jun 2010 15:55:46 GMT

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.

Sub CreatePivotTable()

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)
Else
Set pc = ActiveWorkbook.PivotCaches.Add(xlExternal)
End If

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
Else
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
End If

pc.Connection = connectionString 'OLEDB connection string to the server
pc.commandType = commandType
pc.commandText = commandText
Set pt = pc.CreatePivotTable(TableDestination:=ActiveCell, DefaultVersion:=version)

End Sub

Re: Importing a SAS Dataset directly into an EXCEL Pivot table

FloydNevseta — Fri, 13 Aug 2010 13:03:53 GMT

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.