About CaseySmith

CaseySmith · ‎06-07-2010

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

CaseySmith · ‎06-07-2010

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.

CaseySmith · ‎06-07-2010

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.

CaseySmith · ‎05-21-2010

I debated posting it, but was afraid it would clutter the thread. But since you ask, here goes... I put together the sample VBA code below and ran it in Excel. You have to have SAS Integration Technologies client (standalone, or bundled with AMO and EG) installed on the machine to be able to add the required references to the SAS type libraries in the VBA editor in Excel. There are some help files (.chm) in the Integration Technologies installation folder that should provide some guidance on those interfaces. Good luck! Casey Note: The sample uses the 9.2 interfaces, but I'd expect similar code to work in 9.1.3. The sample also connects to a server defined in metadata, but you should be able to change the code to connect to your local server if preferred. ============================ 'Add reference to "SAS: Integrated Object Model (IOM)..." and "SASObjectManager ..." (optional) Sub SubmitCodeTest() Dim ws As workspace Dim code As String Dim log As String Set ws = ConnectToServerUsingObjectFactory() code = "data tmp; set sashelp.class; run;" log = SubmitCode(ws, code) MsgBox log End Sub Function ConnectToServerUsingObjectFactory() As workspace On Error GoTo ErrHandler 'Requires reference to "SASObjectManager 1.1 Type Library" Dim obObjectFactory As ObjectFactoryMulti2 'Requires reference to "SAS: Integrated Object Model (IOM) (SAS System 9.2) Type Library" Dim obSAS As workspace Dim obObjectKeeper As New SASObjectManager.objectKeeper Set obObjectFactory = CreateObject("SASObjectManager.ObjectFactoryMulti2") obObjectFactory.LogEnabled = True ' Use the SAS Object Manager to establish a SAS workspace object. Set obSAS = obObjectFactory.CreateObjectByLogicalName("SASApp - Logical Workspace Server", "") MsgBox obObjectFactory.GetCreationLog(False, True) MsgBox obSAS.UniqueIdentifier 'Add the workspace to the object keeper obObjectKeeper.AddObject 1, "", obSAS Set ConnectToServerUsingObjectFactory = obSAS Exit Function ErrHandler: MsgBox Err.Description End Function Function SubmitCode(workspace As workspace, code As String) As String Dim ls As SAS.LanguageService Dim logBuffer As String Set ls = workspace.LanguageService 'if we submit asynchronously, we'd have to add a sink and wait to retrieve the log until signalled ls.Async = False ls.Submit code 'get the log logBuffer = ls.FlushLog(100000) 'Could use FlushLogLines to get log instead 'Dim carriageControls() As LanguageServiceCarriageControl 'Dim lineTypes() As LanguageServiceLineType 'Dim logLines() As String 'ls.FlushLogLines 8192, carriageControls, lineTypes, logLines 'For Each line In logLines ' logBuffer = logBuffer & line & vbCrLf 'Next line SubmitCode = logBuffer End Function Function AssignLibref(workspace As workspace, libraryName As String, path As String) 'Assign a libref using the DataService AssignLibref method... workspace.DataService.AssignLibref libraryName, "", path, "" End Function

CaseySmith · ‎05-19-2010

If you are using SAS Add-in for Microsoft Office (AMO), there are a couple custom tasks (written by Chris Hemedinger) that will allow you to simply submit code from AMO. Here is one: http://support.sas.com/documentation/onlinedoc/guide/customtasks/samples/SASProgramRunnerExample.zip UPDATE: Starting in AMO 7.13 release, you can create and run SAS programs in SAS Add-in for Microsoft Office. If you don't have AMO, it's a little more involved, but you can submit code via VBA using the LanguageService. I can send sample code (as-is) if desired. Casey

CaseySmith · ‎04-19-2010

That VBA macro is not interactive -- it doesn't show any UI. It simply refreshes one or more pieces of content (data views, PivotTables, reports, stored processes). To verify that it actually refreshed, you can either change values in the source data or delete some of the values in the results in the worksheet, then run the macro and ensure the latest values are retrieved (Excel doesn't allow you to delete values in a PivotTable, so you'd have to change the source data to verify it).

CaseySmith · ‎04-10-2010

It is odd if "SAS_OfficeAddin" isn't in the list. You may want to look at the top of the list as well since I noticed not all the items are alphabetized. If you can't find it, you can run that macro without adding a reference to "SAS_OfficeAddin" by removing or commenting out the "As SASAddin" by placing a single quote in front of it like so: Dim SasAddinObj 'As SASAddin This may require you turning off options explicit.

CaseySmith · ‎04-09-2010

To use the macro I posted, you have to add a reference to "SAS_OfficeAddin" in the VBA editor (Tools->References, check "SAS_OfficeAddin", OK, then run the macro).

CaseySmith · ‎04-08-2010

There is some documentation in SAS->Help->SAS Add-In for Microsoft Office Help. See the "Working with Visual Basic Code" section under Contents. Here is an example macro: 'For AMO 2.1 or 4.2 'Add reference to "SAS_OfficeAddin" Sub RefreshWorkbook() Dim ComAddin As ComAddin Dim SasAddinObj As SASAddin Set ComAddin = Application.COMAddIns("SAS.OfficeAddin.Loader.ConnectProxy") Set SasAddinObj = ComAddin.Object Set ComAddin = Nothing ' refreshes all the AMO content (tasks, reports, data views, etc. in the workbook) SasAddinObj.Refresh (Workbook) End Sub Additional features (opening data views, pivot tables, reports, running stored processes, etc.) have been added to the scripting inteface in AMO 4.3, due out later this year.

CaseySmith · ‎04-05-2010

Hi David, Technical support should be able to help. I recommend contacting them. Casey

CaseySmith · ‎04-02-2010

Yes, the Add-In for Microsoft Office (starting with version 4.2) supports drill through to detail in PivotTables whose source is an OLAP cube. Excel 2007 also exposes this functionality, so the fact AMO exposes it is more important when using Excel versions prior to 2007.

CaseySmith · ‎01-20-2010

For the local data provider, the Data Source property should be, "A physical directory that contains the SAS data set that you want to access with the connection." (ex. "C:\Program Files\SAS\EnterpriseGuide\4.2\sample\data") Leave the Location empty. If you want to access SAS data through a server instead of the local file system, use the IOM data provider instead of the local data provider. See the "SAS Data Providers: ADO/OLE DB Cookbook" section in the OnlineDocs (http://support.sas.com/onlinedoc/913/docMainpage.jsp) for information on the data providers.

CaseySmith · ‎11-05-2009

Unfortunately, the SAS OLAP provider does not currently support visual totals. We are researching adding this functionality in a future release.

CaseySmith · ‎10-13-2009

No. Any filters and subsetting are applied on the server. Then, Excel pulls only the filtered/subsetted data. So, filtering and subsetting is a good way to increase PivotTable performance. Thanks for asking.

CaseySmith · ‎10-13-2009

On the Properties dialog of the data view (which you can get to from the SAS menu, toolbar, or context menu), on the Advanced tab, there is an option called "Adjust column widths" that is on by default. If you uncheck it, refreshes on that data view will leave the column widths as is.

Online Status	Offline
Date Last Visited	a week ago