SAS® in the Office: IT Works Peter Eberhardt, Fernwood Consulting Group Inc., Toronto, ON

ABSTRACT No matter how sophisticated your data warehousing solution may be you will still get the question “Can I have that in EXCEL?” The Microsoft Office suite is ubiquitous in most large organizations these days. And regardless of how and where the enterprise intelligence computing goes on, the suite of Office products needs to be able integrate with this enterprise intelligence, or the time and energy you spend on ensuring data quality can be quickly lost with a sloppy cut and paste into EXCEL or WORD, or subject to the vagaries of DDE transfers. With the release of v8, SAS introduced Integration Technologies, allowing the unrivalled analytical and business intelligence capabilities of SAS to talk directly with the Microsoft Office suite. With the release of SAS v9 came the Add-In for Office which took the integration with Microsoft a step further. But like many generic user interfaces, the user experience can be somewhat less than ideal. This paper will show how custom user interfaces can be used to access SAS and the Stored Process Server.

There are few SAS programmers and/or analysts who have not heard the refrain ‘Can I have that in an Excel spreadsheet?’. And, once provided, the additional and inevitable refrain ‘Can you run it again with this one change?’. If you have SAS/Access for PC File Formats, generating the spreadsheets is not too big an issue, assuming you have time to make the change and run the programme. And if you do not have SAS/Access for PC File formats, then you have yet another layer of conversion, additional time, and of course the window of opportunity for errors to creep in. And if the results are required in a MS Word compatible format or a MS Access compatible format there are yet other problems you may face. Many people turned to Dynamic Data Exchange (DDE) to try to alleviate these problems. In this paper I will introduce a more robust set of tools aimed at sharing data between SAS and non-SAS applications – SAS Integration Technologies. SAS Integration Technologies was introduced in v8; with SAS v9 came a number of enhancements and improvements, particularly the SAS Add-In For Office, software that integrates into EXCEL (and Word) and facilitates the introduction of enterprise intelligence to the office tool set. But before we look at some of this SAS technology, lets look at some alternatives to exporting data from SAS to MS Office.

I AM IN THE OFFICE BUT SAS IS OUT THERE SAS is running out on the network (say on a Windows 2000 server) and you have to provide a user with a table to be included into an Excel spreadsheet. And SAS in not installed on the user’s computer. What to do? One solution is run SAS on the remote computer, create the output, copy it to a location accessible to the user, and then notify the user it is ready. If the data you need to export are already available in SAS, say in a dataset or standard report already being produced, it can be as simple as wrapping your code with some ODS statements. For example, if you need to export a dataset to EXCEL, the following code will do this nicely:

ODS LISTING CLOSE; ODS CSV FILE = ‘C:\temp\shoes.csv’; PROC PRINT DATA=sashelp.shoes; Run; ODS CSV CLOSE; ODS LISTING;

CSV (Comma Separated Values) is a compact style that EXCEL can import directly; it is useful if you need to export tabular data. If the data you export to EXCEL should also include titles, footnotes, or highly formatted reports, then write to an HTML document, but give the document an XLS extension:

ODS LISTING CLOSE; ODS HTML FILE = ‘C:\temp\shoes.xls’; PROC PRINT DATA=sashelp.shoes; Run; ODS HTML CLOSE; ODS LISTING;

This works, since EXCEL can read HTML documents; giving the XLS extension means that the document will be opened by EXCEL rather than your browser of choice. See the paper by Chevell Parker for an excellent overview of using ODS to generate EXCEL output using ODS. Finally, if your export destination will be WORD, then write your output to an RTF (Rich Text Format) file:

1

Ins & OutsNESUG 18

ODS LISTING CLOSE; ODS RTF FILE = ‘C:\temp\shoes.rtf’; PROC PRINT DATA=sashelp.shoes; Run; ODS RTF CLOSE; ODS LISTING;

Although these are useful additions to a developer’s toolkit, and in some cases all that may be required to answer the inevitable “Can I have that in EXCEL?” refrain, they are not methods that allow you in integrate MS Office applications into your SAS systems.

INTEGRATION TECHNOLOGIES: SEPARATING THE INTERFACE FROM THE ENGINE SAS introduced Integration Technologies (IT) in version 8 of the SAS system. It was an introduction that received little fanfare, yet was a significant move by SAS. Integration Technologies moved SAS from a closed, proprietary system out into the open systems arena; the full analytical and data management power of the SAS system can now be harnessed through your development platform of choice. This means you can develop your front end application using the your corporate development platform, be it Java, C++, VB, the .NET family; even POWERPOINT can leverage the backend analytic power of SAS. You can separate your interface from your engine. Moreover, this can be done without having a PC SAS license on every desktop upon which the application runs. This paper will focus on the SAS 9.1 version of Integration Technologies and the Integrated Object Model (IOM); specifically how to use EXCEL and Visual Basic for Applications (VBA) to access SAS through the IOM. Before delving into the code needed to access SAS from EXCEL, lets look at one of the major components of Integration technologies – the Integrated Object Model (IOM). SAS makes the IOM available to use through IOM servers.

SAS IS IN THE OFFICE WITH ME: THE INTEGRATED OBJECT MODEL The IOM is the heart of Integration Technologies ability to interface with multiple and diverse programming environments; it is a deceptively simple object model that exposed all of the power of SAS to your programming environment. The following figure shows the IOM hierarchy. In SAS v8 there was basically one type of IOM server – a workspace server; much of this paper will deal with accessing a workspace server. In SAS v9 a number of other IOM servers are available; the new one of most interest to us is the SAS Stored Process Server. We will see more about SAS Stored Processes later. The root of the IOM hierarchy is the SAS Workspace object; once instantiated within a client program, the SAS Workspace object can be thought of as a SAS session. Virtually all of the functionality you would have in a batch SAS session is available to you through the workspace object. There are two ways to create a Workspace object – using the SAS Object Manager or using the SAS Workspace Manager. The Workspace Manager was the only way to create a workspace in v8; although it is still available in v9, you should use the Object Manager to instantiate and manage your IOM Workspaces. Doing so will allow you to take advantage of the new features in Integration Technologies. In either case, both methods create the SAS Workspace object on an IOM Server. In the Windows environment (and since we are talking about accessing SAS from EXCEL we will restrict ourselves to the Windows environment) there are three ways the Workspace can be instantiated:

• Through local COM if the SAS Server runs on the same machine as the client • Through DCOM if the SAS Server runs on another machine that supports DCOM • Through the IOM Bridge for COM if the SAS Server runs on another machine that does not support

COM/DCOM functionality (Unix/OS390). Although you can use COM/DCOM when doing Windows to Windows communication, it is better to use the IOM Bridge, if for no other reason than you need not change your code should your server change from a Windows hardware platform to non-Windows platform. Moreover, there are a number of IT advanced features available when you use the IOM Bridge protocol; we will not be looking at these features in this paper.

2

Ins & OutsNESUG 18

Figure 1 The IOM Hierarchy Regardless of how the SAS Workspace is created (COM, DCOM, IOM Bridge), it still offers the same set of services – DataService, FileService, LanguageService, and Utilities.

LANGUAGESERVICE The LanguageService component provides methods to submit SAS code to the IOM Server as well as retrieve log and list outputs. In addition to submitting SAS code, the LanguageService allows you to execute SAS Stored Processes – a special format of a SAS program available on the server. Using SAS Stored Processes allows you to have centralized SAS programs that can be invoked by a multitude of SAS clients. Beyond executing SAS code, Stored Processes can also return streaming output (as is the case with stored processes executed from the SAS Add-in for Office) or ResultPackages. A ResultPackage is a file that can contain heterogeneous content – SAS data sets, ODS output, external files, etc. Once returned from the execution of the Stored Process, the ResultPackage can be rendered using the Utilities Service. In order to monitor the progress of your SAS Stored Process, the IOM will raise events that the LanguageService can trap; common events such as DATA STEP or PROC begin and DATA STEP and PROC end events can be raised and trapped. Moreover, errors in the SAS execution can raise events that can also be trapped through the LanguageService.

DATASERVICE The DataService is used primarily to manage SAS librefs – references to SAS libraries, be they SAS libraries or external (e.g. ODBC, ORACLE) libraries. You can assign, deassign or iterate through SAS librefs. All librefs managed by the IOM are relative to the server upon which the workspace is running.

FILESERVICE The FileService is used primarily to manage SAS filerefs, external file references. The FileService can not only assign and deassign SAS filerefs, it can also be used to read and/or write to these external files. All filerefs managed by the IOM are relative to the server upon which the workspace is running.

UTILITIES The Utilities service provides methods to access much of the non-core functionality of SAS. Two of the most useful component of the Utilities service is the Format Service and the ResultPackage Service. The FormatService allows you to apply SAS formats to the data returned using the data access component, while the ResultPackageService allows you to render the packages returned from a Stored Process. We will also using the Utilities service to access information about the server upon which the workspace is executing.

3

Ins & OutsNESUG 18

DATA COMPONENT The DataService and FileService provide methods to access SAS libraries through librefs or host system files through filerefs. The full range of library and file manipulation tools is available through these services. Microsoft’s ADO/OLE DB data model is used to share data between the client application and the SAS IOM server. The SAS IOM works with Microsoft’s ADO/OLE DB, ADO.NET or a JDBC data model to share data between the client application and the SAS IOM server. The ADO/OLE DB model will work in any Windows environment, while the ADO.NET will only work in a .NET environment. The simpler ActiveX Data Object (ADO) model is shown in Figure 2.

Figure 2 The ADO Hierarchy

MICROSOFT ADO In order to share data between SAS and Excel we will need to understand a bit about ADO. For the purposes of this paper there are two objects of interest – the Connection object and the Recordset object. The Connection object provides properties to define the source of the data, and methods to manage the link between the client to the data source. The Recordset object uses the Connection object to return data to the client. The Fields collection of the Recordset object provides data about the contents of the recordset.

REQUIREMENTS In order to follow the examples in this paper, you will have to have SAS v9.1x client components installed; as part of the installation procedure be sure the Integration Technologies components are installed. Microsoft Data Access Components (MDAC) v2.1 or higher should also be installed; normally this will be installed along with SAS Integration Technologies. You will also have to have one of the SAS EI bundles installed with a running metadata server, a workspace server and a stored process server. In addition, to facilitate the communication between a Windows application and the metadata server you should run the ITCONFIG utility to create the Integration Technology configuration file on your workstation. The examples that follow were developed under Windows XP Professional sp2 using SAS v9.13. The office products are all from Office 2000. For a copy of an EXCEL workbook with the complete code use my contact information at the end of the paper. In order to use the SAS IOM within Excel we will need to make sure that Excel has the proper references to the objects. To do this, start Excel and follow these steps From an empty spreadsheet open the Visual Basic Editor (Tools…Macro…Visual Basic Editor) Create a new module (Insert…Module) Add the references to the SAS IOM and the SAS Object Manager (Tools…References – in the dialogue box, scroll down and select the SAS objects) Add the references to the Microsoft ActiveX Data Objects (Tools…References – in the dialogue box scroll down to Microsoft ActiveX Data Objects and select the highest version available)

4

Ins & OutsNESUG 18

A similar process will need to be followed for each of the other Office products.

A DAY AT THE OFFICE Regardless of the type of problems we are trying to solve, or the programming environment we are using to them, there are a number of common tasks we have to perform. This paper will go through these tasks with an emphasis on the IOM programming steps, not on the user interface or data display. The common tasks we will explore are:

• how to create an IOM Workspace Server • how to submit SAS code • how to retrieve the SAS log • how to retrieve a SAS dataset • how to submit a SAS Stored Process

First, whenever using VBA modules, always put Option Explicit as the first line in the module. This option requires you to declare all variables used. If this option is not set errors in the form of misspelled variables can easily creep into your programme.

CREATING A SAS WORKSPACE SERVER The original (SAS v8) method to create a workspace was to use the Workspace Manager. The advantage of this method is that it is slightly simpler and does not require the use of a metadata server. The downside is that you get none of new features built into v9, and more importantly, no access to the central metadata; although using Workspace Manager still works in v9, it is not recommended. The following code example shows creating the workspace through the v9 SASObjectManager. I will step through this code in a little more detail than later code examples. When reading through the code keep in mind that all VBA statements must be on one line, unless there is an explicit line continuation character (the ‘_’). In the process of cutting and pasting code some lines wrap in this document that are on one line in the VBA environment.

Option Explicit ' make the workspace and object factory global to the form Public sasWS As sas.Workspace ' this is the v8 way to do it ' Public sasWM As SASWorkspaceManager.WorkspaceManager ' this is the v9 way Public sasOF As SASObjectManager.ObjectFactory Public sasOK As SASObjectManager.ObjectKeeper Public sasSRV As SASObjectManager.ServerDef Public cnnIOM As ADODB.Connection ' i've noticed on some versions of excel the 'with events' does not work ' if this is the case, comment out the following statement Public WithEvents sasLSevents As sas.LanguageServicePrivate ' and uncomment this one. Example 3 will not work in this case ' Public sasLSevents As SAS.LanguageService Sub cbRunCode0_Click() Dim xmlInfo As String Dim sasLoginDef As SASObjectManager.LoginDef Dim sasMetaDef As SASObjectManager.ServerDef ' connect using the ObjectFactory ' this will automatically check the configuration file from itconfig ' and make a connection to the metadata server cbShowCode0.Visible = False Me.MousePointer = fmMousePointerHourGlass DoEvents If ofOpen = False Then Set sasOF = New SASObjectManager.ObjectFactory ' find out which config file we are using xmlInfo = sasOF.GetSystemMetadataFile

5

Ins & OutsNESUG 18

' get the info on the metadata server and user used to login ' not necessary, just to show some of the info we can get Set sasLoginDef = sasOF.GetOMRLoginDef Set sasMetaDef = sasOF.GetOMRServerDef ofOpen = True End If If wsOpen = False Then Set sasSRV = New SASObjectManager.ServerDef sasSRV.DomainName = "fernwood.local" sasSRV.MachineDNSName = "gandalf01" sasSRV.Protocol = SASObjectManager.Protocols.ProtocolBridge sasSRV.Port = 8591 ' the workspace server port ' should get the username and password from the form (or INI file) Set sasWS = sasOF.CreateObjectByServer("TestWorkspace", True, sasSRV, "fernwood\sasdemo", "sasdemo") ' add the workspace to the object keeper, then get the host properties of the new server Set sasOK = New SASObjectManager.ObjectKeeper sasOK.AddObject 1, "TestWorkspace", sasWS Dim swinfo() As String Dim hwinfo() As String Dim sasverDesc As String Dim OSDesc As String Dim jobDesc As String Dim CPUDesc() As String Dim id As String Dim cpuLine As Variant sasWS.Utilities.HostSystem.GetInfo swinfo, hwinfo id = sasWS.UniqueIdentifier sasverDesc = "SAS Version: " & swinfo(HostSystemSoftwareInfoIndex.HostSystemSoftwareInfoIndexSASVersion) OSDesc = "OS: [" & swinfo(HostSystemSoftwareInfoIndex.HostSystemSoftwareInfoIndexOSFamily) OSDesc = OSDesc & " " & swinfo(HostSystemSoftwareInfoIndex.HostSystemSoftwareInfoIndexOSName) OSDesc = OSDesc & " " & swinfo(HostSystemSoftwareInfoIndex.HostSystemSoftwareInfoIndexOSVersion) & "]" jobDesc = " Server UserID: " & swinfo(HostSystemSoftwareInfoIndex.HostSystemSoftwareInfoIndexServerUserID) & vbCrLf jobDesc = jobDesc & " Client UserID: " & swinfo(HostSystemSoftwareInfoIndex.HostSystemSoftwareInfoIndexClientUserID) & vbCrLf jobDesc = jobDesc & "Job or Process: " & swinfo(HostSystemSoftwareInfoIndex.HostSystemSoftwareInfoIndexJobOrProcessID) & vbCrLf Dim nProc As Integer, i As Integer, maxProc As Integer maxProc = UBound(hwinfo, 1) ' Determine number of rows (=number of CPUs) ReDim CPUDesc(maxProc) For i = 0 To maxProc ' Fix subscript order on next three stmts CPUDesc(i) = "CPU " & Str(i) & "==> " CPUDesc(i) = CPUDesc(i) & " ModelName: " & hwinfo(i, HostSystemHardwareInfoIndex.HostSystemHardwareInfoIndexModelName) CPUDesc(i) = CPUDesc(i) & " ModelNumber: " & hwinfo(i, HostSystemHardwareInfoIndex.HostSystemHardwareInfoIndexModelNumber) CPUDesc(i) = CPUDesc(i) & " SerialNumber: " & hwinfo(i, HostSystemHardwareInfoIndex.HostSystemHardwareInfoIndexSerialNumber) Next i txtResults0.Text = sasverDesc + vbCrLf txtResults0.Text = txtResults0.Text + OSDesc + vbCrLf txtResults0.Text = txtResults0.Text + jobDesc + vbCrLf

6

Ins & OutsNESUG 18

For Each cpuLine In CPUDesc txtResults0.Text = txtResults0.Text + cpuLine + vbCrLf Next cpuLine Set sasLSevents = sasWS.LanguageService wsOpen = True End If If cnOpen = False Then Set cnnIOM = New ADODB.Connection cnnIOM.Open "Provider=sas.iomprovider.1; SAS Workspace ID=" & sasWS.UniqueIdentifier cnOpen = True End If cbShowCode0.Visible = True Set sasLoginDef = Nothing Set sasMetaDef = Nothing Me.MousePointer = fmMousePointerDefault End Sub

There are two parts to this code snippet. First in the set of common global variable declarations and the second is the event procedure (Sub cbRunCode0_click()) behind a button on a form. Although not good programming practice, declaring these common variables as global makes it easier to demonstrate the SAS IOM calls. The main global variables are the SAS workspace object (sasWS), the SAS LanguageService object (sasLSevents), and the ADO connection object (cnnIOM). The event procedure cbRunCode0_Click() creates a new SAS ObjectFactory object

Set sasOF = New SASObjectManager.ObjectFactory Note that once the object is created we can query its properties; in this example we get the name of the configuration file (created by the ITCONFIG utility) that is used to connect to the metadata sever, as well as the username that is used to in the connection to the metadata server. The important thing here is we are automatically connected to the metadata server. We use a SAS server definition object (sasSRV) to identify the hardware server upon which we want the SAS workspace server to execute. The code

Set sasSRV = New SASObjectManager.ServerDef sasSRV.DomainName = "fernwood.local" sasSRV.MachineDNSName = "gandalf01" sasSRV.Protocol = SASObjectManager.Protocols.ProtocolBridge sasSRV.Port = 8591 ' the workspace server port ' should get the username and password from the form (or INI file) Set sasWS = sasOF.CreateObjectByServer("TestWorkspace", True, sasSRV, _ "fernwood\sasdemo", "sasdemo")

tells the SAS Object Manager to create a SAS workspace on the server called gandalf01 in the domain fernwood.local; the workspace server will listen on port 8591 and will use the IOM Bridge protocol to communicate. Since it is possible to create multiple workspace objects in a programme, one method to help identify workspace objects is give each a name; in this case it is called TestWorkspace. In a real application the server definition, the username (fernwood\sasdemo) and the password would come from configuration files and/or prompts. Most of the remainder of the code in this snippet shows how to use some of the Utilities services of the workspace; in particular we gather information about the hardware and software configuration of the physical server (gandalf01) upon which the workspace server (TestWorkspace) is running. In addition, we create a LanguageService object so we can execute SAS code later

Set sasLSevents = sasWS.LanguageService plus an ADO connection object so we can transfer datasets from the SAS workspace to EXEL

cnnIOM.Open "Provider=sas.iomprovider.1; SAS Workspace ID=" & sasWS.UniqueIdentifier In this simple bit of code we have set up a connection to our metadata server, created a workspace object along with a LanguageService object that will allow us to submit SAS code, and an ADO connection object that will allow us to transfer datasets from SAS to EXCEL. The SAS engine is now in the EXCEL interface!

SUBMITTING SAS CODE

7

Ins & OutsNESUG 18

With a SAS workspace waiting to do some work and a LanguageService object waiting to give it something to do, lets look at some ways to submit code. One of the first things we need to decide is whether we want to submit our code and have the application wait on the SAS code to compete – as is the case when submitting code in the SAS Display Manager window, or whether we want to submit the code and free the application to do other things while SAS is executing. In these examples we will submit our code and wait for SAS to finish by turning off the asynchronous behaviour.

' first, turn off async so the process runs before we try to retrieve sasWS.LanguageService.Async = False

One simple way to execute code is the write it right into the programme as in:

' assign the libref using the Dataservice, catch an error if not datasouce On Error GoTo errNoDataSource Set libref = sasWS.DataService.AssignLibref("nesug", "ODBC", "", "dsn=northwind") ' submit a data step accessing the libref sasLSevents.submit "data customers; set nesug.customers; ;run;" ' print the results sasLSevents.submit "proc print data = customers ; run;" sasLSevents.submit "data test; do customer=1 to 10;quantity=customer*customer; pizza='Pepperoni'; orderdate=date();output;end;format orderdate yymmdd10.;run;" ' Use LanguageService to get the dictionary info about work.test sasLSevents.submit "proc sql; create table colInfo as select * from sashelp.vcolumn where libname='WORK' and memname = 'TEST'; quit;"

We can submit data step code and as well as SAS procs. In addition, we can do multiple submits, just as we can with the SAS Display Manager. Note that these examples should each be on a single line. Although in these examples the SAS code is submitted as constant strings, string variables could have been created – say from values in the spreadsheet – and these string variables then submitted. This snippet also demonstrates the call to assign a SAS libref. The libref does not have to be for a SAS dataset, it can be for any data engine that is licensed on the server. Also, and very important, the location of the data is relative to the workspace server. In this case the ODBC definition for northwind must be defined on the server gandalf01. Although there can be a place for such simple code submissions, the real power SAS code comes when we access SAS Stored Processes, but before we do that, lets see how we examine the SAS log

RETRIEVING THE SAS LOG As a diagnostic and debugging tool, the SAS log is invaluable. When programming to the IOM you can retrieve the SAS log (or the SAS listing, though it is not covered here). One important point to note: once you retrieve the SAS log (or list), it is lost unless you have saved it in your programme. The code to capture the log is simple:

Dim sasLog As String sasLog = sasLSevents.FlushLog(100000) MsgBox sasLog, vbOKOnly, "Here is the LOG"

The argument to FlushLog is the maximum number of characters to retrieve. Although this example makes only one call to FlushLog, you should make repeated calls to FlushLog until the length of the string returned is 0; that is, there were no more log lines to return. Remember, once you retrieve the SAS log, unless you save it in your programme, it is gone – the same as hitting the Clear button in Display Manager. Once we have examined our log and found we have no errors we will want to return some of the data we just created.

RETRIEVING A SAS DATASET In the section SUBMITTING SAS CODE above we created a number of different datasets, and it is likely that if we created them, we will want them in EXCEL. In the following code snippets we will be using the IOM data interface – Microsoft’s Active Data Objects (ADO). You will remember we created a global connection object (cnnIOM) after we created the workspace object; here we will use this connection object along with a related object – a recordset; in simple terms, a recordset is essentially the same thing as a SAS dataset

' Associate the Recordset object with the dictionary info Set rsCOLS = New ADODB.Recordset rsCOLS.Open "work.colInfo", cnnIOM, adOpenDynamic, adLockPessimistic, _ ADODB.adCmdTableDirect If Not (rsCOLS.BOF And rsCOLS.EOF) Then rsCOLS.MoveFirst Do While Not rsCOLS.EOF If Len(rsCOLS!Format) > 0 Then sFormat = sFormat & "+" & rsCOLS!Name & "=" & rsCOLS!Format & ", "

8

Ins & OutsNESUG 18

End If rsCOLS.MoveNext Loop End If If Len(sFormat) > 0 Then sFormat = Left$(sFormat, Len(sFormat) - 2) End If ' use the command connection ' so we can take advantage of some custom properties ' in this case use sas formats Set cmdSAS = New ADODB.Command Set cmdSAS.ActiveConnection = cnnIOM cmdSAS.CommandText = "TEST" If Len(sFormat) > 0 Then cmdSAS.Properties("SAS Formats") = sFormat End If ' Associate the Recordset object with the SAS data set. Set rsSAS = New ADODB.Recordset rsSAS.Open cmdSAS, , , ADODB.adLockReadOnly, ADODB.adCmdTableDirect If Not (rsSAS.BOF And rsSAS.EOF) Then ' FUNCTION UTILITY.DisplayRS uses the Excel CopyFromRecordet method DisplayRS "sheet1", rsSAS ' FUNCTION UTILITY.DisplayRS uses the recordset move methods DisplayRSFields "sheet2", rsCOLS End If

This snippet shows how to use transfer a SAS dataset to a recordset by specifying the SAS dataset

rsCOLS.Open "work.colInfo", cnnIOM, adOpenDynamic, adLockPessimistic, _ ADODB.adCmdTableDirect

This statement returns the SAS dataset work,colInfo into the ADO recordset rsCOLS using the connection cnnIOM we created earlier; the remaining arguments to the call gives ADO more information about how we want the recordset opened – the online help will describe the arguments. This example also shows most of the major recordset navigation methods: MoveFirst – moves to the first record in the recordset MoveLast – moves to the last record in the recordset MoveNext – moves to the next record in the recordset MovePrev – moves to the previous record in the recordset One of the ‘gotchas’ of dealing with recordsets is that an error will be raised if you attempt and of the navigation methods on an empty recordset. As a result, a common construct you will see looks like:

If Not (rsCOLS.BOF And rsCOLS.EOF) Then The BOF property is a flag saying we are positioned at the beginning of the file (BOF) and the EOF property is a flag saying we are positioned at the end of the file (EOF). If we are at both the beginning and the end then there must be no records in the recordset; the above test says if we are not at both the beginning and the end then we must have records to process Data returned in the above manner will not have any of the SAS formats applied. In order to have the formats applied we must first build a string with the column name and the appropriate format. If you recall, one of the proc steps we submitted earlier was a PROC SQL step against the dictionary tables to return (among other things) the format of the columns in the SAS dataset TEST we will want to retrieve. We use this metadata, retrieved in rsCOLS to create the appropriate format string

sFormat = sFormat & "+" & rsCOLS!Name & "=" & rsCOLS!Format & ", " Since Microsoft’s ADO does not know anything about SAS formats, SAS created a custom property for the connection object (SAS Formats) that tells ADO how to deal with them.

cmdSAS.Properties("SAS Formats") = sFormat Since we are using a custom property we must also use a slightly different call to retrieve the SAS dataset into a recordset

rsSAS.Open cmdSAS, , , ADODB.adLockReadOnly, ADODB.adCmdTableDirect

SUBMITTING A STORED PROCESS Stored Processes are central to programming to the IOM; they are truly the major element that separates the interface from the engine. Essentially, a SAS stored process is a SAS programme with parameters. It is also a programme that can optionally return results. And SAS being SAS, there is more than one type of stored process.

9

Ins & OutsNESUG 18

The first type was available in SAS v8 and still works in SAS v9; this type of stored process is called an IOM Direct Interface Stored Processes. To run this you do not need access to a metadata server or a StoredProccess server; it can only be executed on a workspace server. It can take parameters and return only one type of result – a SAS ResultsPackage. For more information on ResultsPackages see the SAS Publishing Framework documentation. The second type of SAS stored process, simply called a Stored Process, needs a metadata server and a StoredProcess server. It can take arguments and can return either a ResultsPackage or streaming output. It is this latter stored process that the SAS Add-In executes, and the streaming output is written to a worksheet. The code snippets that follow show how to execute both types. The first example shows executing an IOM Direct Interface Stored process.

' create the stored process service Set sp = sasWS.LanguageService.StoredProcessService ' tell it where to find the stored processes sp.Repository = "file:c:\examples\procs" ' and execute the stored process sp1.sas with no args sp.Execute "spODS", "ODSHTML=1 ODSRTF=1" ' Associate the Recordset object with the SAS data set. Set rsSAS = New ADODB.Recordset rsSAS.Open "freqResults", cnnIOM, adOpenDynamic, adLockPessimistic, _ ADODB.adCmdTableDirect

Here we first to tell the StoredProcess service where to find the stored process, then execute a specific stored process spODS and supply 2 arguments to the stored process. With this type of stored process the arguments are passed as name/value pairs (ODSHTML=1). Although this type of stored process cannot return streaming output that can be displayed on a worksheet, using recordsets we can easily return data and display them on a worksheet. Note that with the above stored process we needed to know everything about the stored process – name, location, arguments etc. This is because we did not have access to the metadata about the stored process. When we move to the v9 stored processes we require a metadata server, but as a result our programmes can go out on a journey of discovery to find which stored processes are available to us, what arguments they take and what sort of results, if any, they return. The commands:

Set spManager = New SASStoredProcessService.Manager spData = spManager.ListAvailableProcesses

create a Stored Process Manager which has access to the stored process metadata. We can use this to get a list of stored processes that are available to the programme. For any stored process to which we have access we can also get a list of any parameters that may be required. This is shown in the following snippet.

Set spinfo = spManager.GetProcessInfo("/Samples/Stored Processes/spBaseBall") parameterGroups = spinfo.ListParameterGroups Dim grp As SASStoredProcessService.ParameterGroupInfo For i = LBound(parameterGroups, 1) To UBound(parameterGroups, 1) Set grp = parameterGroups(i) Dim parm() As Variant parm = grp.ListParameters Dim aParm As SASStoredProcessService.ParameterInfo Dim j As Integer For j = LBound(parm, 1) To UBound(parm, 1) Set aParm = parm(j) Debug.Print aParm.Name Next j Next i

Here we were explicit about the stored process we were interested in; we could have used an item from the list of available processes we retrieved earlier. Note that the name of the stored process (/Samples/Stored Processes/spBaseBall) is the name in the metadata server. This example retrieves all the parameter groups available for the stored process, then for each group steps through each parameter and displays the name in the VBA immediate window. This sort of discovery process can be used to build dynamic input prompts for the stored process. I suspect that SAS has done just that in the Add-In for Office. Once you know the stored process you want to execute, the call to execute it looks like:

Set spInstance = spManager.CreateByName("/Samples/Stored Processes/spBaseBall") spInstance.SetParameterValue "type", VBA.UserForms(frmIndex).rptType spInstance.SetParameterValue "year", VBA.UserForms(frmIndex).rptType Set spExecution = spInstance.Execute("")

Again in this example we were explicit in the metadata name and the parameters – we could have used variables

10

Ins & OutsNESUG 18

from our discovery process above. Also note that the parameters are getting values from an EXCEL userform created to supply parameters to this stored process; it is also possible to supply values from spreadsheet cells in the workbook. If the stored process is returning streaming output we can capture it and decide how to display it. In the following snippet the output is saved to a file

Set spReader = spExecution.SelectStream("_WEBOUT") charsToRead = spReader.BytesAvailable tfilename = makeTempFile & ".HTM" Set fsObject = New Scripting.FileSystemObject Set fsFile = fsObject.CreateTextFile(tfilename, True) charsRead = 0 While (charsRead < charsToRead) chars = spReader.Read(stream) For i = 0 To UBound(stream) fsFile.Write Chr$(stream(i)) Next i charsRead = charsRead + chars Wend fsFile.Close spReader.Close

Since we are unlikely to want to build an HTML parser to display the output in EXCEL, we would probably use one of EXCEL web functions to display the file. In the above code snippets we have seen a number of SAS objects, methods and properties in action. Lets look briefly at how to find out a bit more about them while in the EXCEL environment as well as out.

LET’S BE OBJECTIVE How do you find the options and properties for these objects we are using? If manuals were regularly distributed with software, you could read the manual. Now manuals are replaced with on-line help. Unfortunately, navigating the help files is not always easy or productive. Fortunately there is a way to get the methods and properties of the objects – the VBA object browser. Within the VBA editor select View… Object Browser; the shortcut key to the Object Browser id F2

The Object Browser There are also Windows help files (.CHM) distributed. In a normal install these files are in: C:\Program Files\SAS\Shared Files\Integration Technologies In my experience these help files are not nearly as helpful as they could be. However, between them and the Object Browser it is usually possible to figure out (with some trial and error) how the methods work.

WHY WOULD I DO ALL OF THIS WHEN THE ADD-IN IS AVAILABLE Having the Add-In for Office automating the process of integrating the business intelligence in SAS with the familiar MS Office environment in a flexible and easy manner, there is little need to create your own custom application. But as we all know, flexible and easy interfaces do not always provide detailed and very specific formats we are often

11

Ins & OutsNESUG 18

required to deliver. But when the CFO says “I want my spreadsheet exactly like this and I want it to reflect the options that are already on another worksheet.” you might find this approach better than tell her to use the Add-In and then have to look for a new job.

BEFORE YOU GO HOME These examples have been kept simple to highlight a few of the aspects of SAS Integration Technologies. By no means are they exhaustive of the power of SAS Integration Technologies. However, they should start you on your way. For an EXCEL spreadsheet which demonstrates using Integration Technologies contact me by email.

REFERENCES For a complete overview of SAS Integration Technologies see support.sas.com/rnd/itech Eberhardt, Peter and Richard DeVenezia “Through the Looking Glass: Two Windows into SAS” Proceedings of the Thirtieth Annual SAS User Group International Conference Green, John (1999) Excel 2000 VBA Programmer’s Reference Wrox Press, Birmingham Jennings, Roger (1999) Database Developer’s Guide with Visual Basic® 6 SAMS, Indianapolis IN Key, Darren and David Shamlin “Using SAS® Data To Drive Microsoft Office” Proceedings of the Twenty-Seventh Annual SAS Users Group International Conference Parker, Chevell “Generating Custom Excel Spreadsheets using ODS” Proceedings of eighth-Eighth Annual SAS Users Group International Conference

CONTACT INFORMATION Peter is SAS Certified Professional V8, SAS Certified Professional V6, and SAS Certified Professional - Data Management V6. In addition his company, Fernwood Consulting Group Inc. is a SAS Alliance Partner. If you have any questions or comments you can contact Peter at: Fernwood Consulting Group Inc., 288 Laird Dr., Toronto ON M4G 3X5 Canada Voice: (416)429-5705 e-mail: peter@fernwood.ca

12

Ins & OutsNESUG 18