Displaying Metadata That Is Specific to SAS Data Sets

Goal

You want your application to display metadata that is specific to SAS data sets.

This recipe applies to the local, SAS/SHARE, IOM, and Base SAS providers. Sample code for ADO is included.

ADO Implementation

How Metadata Is Exposed

Available metadata for a SAS data set includes persisted formats and informats, number of records, and whether an index exists. To extract the metadata, you use extensions to the COLUMNS and TABLES schema rowsets.

Note: For more information, see COLUMNS Schema Rowset and TABLES Schema Rowset.

Sample Code for Displaying Information about Formats and Informats

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

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset

' Get schema information for Sasuser.Shoes. 
Set obRecordset = obConnection.OpenSchema(adSchemaColumns, Array(Empty, Empty, "Sasuser.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

A Closer Look at the Parameters for the OpenSchema Method

The following line of code specifies the parameters for the OpenSchema Method:

Set obRecordset= obConnection.OpenSchema(adSchemaColumns, Array(Empty, Empty, "SASUSER.SHOES"))

For the first parameter, which is Query Type, the sample code specifies adSchemaColumns. adSchemaColumns returns information for all columns on all data sets in the open connection.
For the second parameter, which is Criteria, the sample code passes in an array that limits the returned information to just SASUSER.SHOES columns. To achieve this result, the array specifies a nonempty value for the TABLE_NAME, which is the third constraint available for adSchemaColumns. (adSchemaColumns has four available constraints: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and COLUMN_NAME.)

Tip

To return metadata for all data sets in the open connection, write similar code and specify adSchemaTables (instead of adSchemaColumns) as the value of the QueryType parameter.

OLE DB Implementation

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 custom OLE DB rowset interfaces listed below. Schema rowset extensions and these custom interfaces return the same information.