Accessing Third-Party Data through SAS/ACCESS Engines

Goal

You want to access third-party data stores that are available through your licensed SAS/ACCESS engines.
This recipe applies to the SAS/SHARE, IOM, and Base SAS providers. Sample code for ADO is included.

Implementation

Assigning a Libref for a SAS/ACCESS Engine

An engine is a component of SAS software that is used to read from or write to a source of data. There are several types of SAS engines, including engines that SAS/ACCESS software uses to connect to a variety of data sources other than Base SAS. To access third-party data that is available through your licensed SAS/ACCESS engines, you must assign a libref for the specific SAS/ACCESS engine. How you assign the libref depends on the provider.
  • The IOM provider is the only provider that can be used to directly assign a libref, as illustrated by the sample code.
  • If you are using the SAS/SHARE provider, the libref must have been assigned when the SAS/SHARE server was started.
  • If you are using the Base SAS provider, you must specify the libref in the start-up script that is used by the provider to start a SAS session (see Connecting to Local Data (Single-User Server)).

Sample Code for Accessing Third-Party Data Using the IOM Provider

The following sample Visual Basic code uses the ADO Command and Recordset objects in order to access Oracle data. The record set is opened for read-only access as indicated by adLockReadOnly. 
' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset
Dim obCommand As New ADODB.Command

obCommand.ActiveConnection = obConnection

'Assign a libref by executing a LIBNAME statement that identifies the SAS/ACCESS engine.
obCommand.CommandType = adCmdText
obCommand.CommandText = "libname mylib oracle user=todd password=king path=oraclev7;"
obCommand.Execute

'Open the data set for read-only access.
obRecordset.Open "mylib.dept", obConnection, adOpenStatic, adLockReadOnly, adCmdTableDirect
Note: The syntax that you use in your LIBNAME statement depends on the SAS/ACCESS engine that you are using and on your operating environment. SAS/ACCESS engines are implemented differently in different operating environments. See the documentation for your DBMS for more information.
Note: If you are writing directly to the OLE DB interface, use OLE DB rowset methods in order to read the SAS/ACCESS data set. To open the rowset for read-only access, set the DBPROP_IRowsetChange property to False.

See Also

Specifying a Libref to Use with the IOM Provider
Copyright © SAS Institute Inc. All rights reserved.