Displaying Metadata Specific to SAS

In this recipe, you learn how to display metadata that is specific to SAS data sets. Sample code for ADO is included.

Applies to:	SAS Local Data Provider, SAS/SHARE Data Provider, SAS IOM Data Provider
Implement using:	ADO or OLE DB

Overview

The SAS data providers expose SAS metadata—such as formats and informats—through extensions to schema rowsets.

Note: For information about the specific metadata that is available through schema rowsets, see the descriptions of the COLUMNS and TABLES schema rowsets.

The following example shows how to display information about SAS formats and informats that are persisted on a data set named Shoes in the Sashelp library.

Note: For information about how to open an ADO Connection object, see Opening an ADO Connection Object.

' Open a connection.
Dim obConnection As ADODB.Connection

Set obConnection = New ADODB.Connection

obConnection.Provider = "sas.IOMProvider.1"
obConnection.Properties("Data Source") = "_LOCAL_"
obConnection.Open

Dim obRecordset As ADODB.Recordset

' Get schema information for SASHELP.SHOES. 
Set obRecordset= obConnection.OpenSchema(adSchemaColumns, Array(Empty, Empty, "sashelp.shoes"))

' Display fields pertaining to SAS formats and informats.
Do Until obRecordset.EOF
   Debug.Print "Table name: " & obRecordset!TABLE_NAME & vbCr & _
         "Column Name: " & obRecordset!COLUMN_NAME & vbCr & _
         "Format name: " & obRecordset!FORMAT_NAME & vbCr & _
         "Format length: " & obRecordset!FORMAT_LENGTH & vbCr & _
         "Informat name: " & obRecordset!INFORMAT_NAME & vbCr & _
         "Informat length: " & obRecordset!INFORMAT_LENGTH
         obRecordset.MoveNext
Loop

ADO Details

In the OpenSchema method in the sample code, the following parameters are set:

The schema type that is used for the QueryType parameter is adSchemaColumns, which returns information for all columns on all data sets in the opened connection.
The Criteria parameter passes in an array that limits the returned information to just Sashelp.Shoes columns. To achieve this result, the array specifies a non-empty value for the TABLE_NAME, which is the third constraint available for the adSchemaColumns schema type. (The adSchemaColumns schema type has four available constraints: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME.)

To return metadata for all data sets in the opened connection, you can write similiar code using the schema type adSchemaTables as the QueryType parameter.

Note: See About Schema Rowsets for information about how the SAS Data Providers implement schema rowsets.

OLE DB Details

When you are programming directly to the OLE DB interface, you can obtain SAS metadata either through schema rowset extensions, or through one of the following custom OLE DB rowset interfaces. Schema rowset extensions and these custom interfaces return the same information.

Resources

Displaying Metadata Specific to SAS

Overview

ADO Details

OLE DB Details