Using Formats When You Read Data

In this recipe, you learn how to prepare the local, SAS/SHARE, and IOM data providers to access SAS formatting features. Sample code for the ADO recipe is included.

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

Overview

All four providers implement a customized recordset property called "SAS Formats". ADO applications use this property to access SAS formatting features. For an explanation of the ADO implementation, see ADO Details.
OLE DB applications use customizations to the OLE DB interfaces to access SAS formatting features. For information about calling a SAS provider directly through the OLE DB interfaces, see OLE DB Details.

ADO Details

SAS implements a set of formats that can be used to transform basic character and numeric values into formatted character values. In ADO, formats are controlled with the "SAS Formats" customized recordset property. This property takes a string value that specifies the format you want to use, and specifies any optional overriding information. The string value also lists one or more columns to which the format should be applied. Use the following form to enter the string value:

[+]COLUMN[=FORMAT[w].[d]]

Following is an explanation of the form:

+ is an optional modifier that will expose the column in two conditions: formatted and unformatted.
COLUMN is the name of a column in the recordset (which is the same as the variable name in the SAS data set). (If you need to specify more than one column, you must separate the column names with commas).
FORMAT is the name of the format that you want to apply.
w is the width that you want to use with the format.
d is the number of decimal places (if any) to include with numeric formats.

The order in which the columns are listed in the string does not affect the order in which they are returned by an ADO Recordset object.

For example, assume that a data set has three variables that appear in the following order:

SALEDATE, a SAS date with a default format of MMMYY.
QUANTITY, a SAS numeric value with no default format.
PRICE, a SAS numeric value with no default format.

The following string value is based on the sample data set:

SALEDATE=MMDDYY8.,+PRICE=DOLLAR8.2

The results appear in the following four columns on the ADO Recordset object:

SALEDATE as a string column with the MMDDYY8. format applied.
QUANTITY as a numeric column with no format applied.
PRICE as a numeric column with no format applied.
PRICE_DOLLAR8.2 as a string column with the DOLLAR8.2 format applied.

Note: The fourth column was constructed through the use of the + modifier; it is not persisted beyond the life of its exposure on the ADO Recordset object.

How Default Formats are Applied

In the absence of explicit format information, or if you specify "_ALL_" as the "SAS Formats" property, the SAS providers apply format properties to all columns according to the following rules:

If no format name, width, or decimal value is specified with a column name, then the providers default to using the format information that is persisted with the data set.
If no format specification is stored in the data set, then the providers use the system defaults. The system defaults are W.D for numeric columns and $CHAR. for character columns.

How the + Modifier Works

To see the same physical column of data exposed as two columns, one formatted and one unformatted, prefix the column name with the plus (+) sign modifier. When you make this request, the formatted column appears as a constructed, temporary column named COLUMN_FORMAT. The formatted column is not persisted beyond the life of its exposure on the ADO Recordset object.

To see all columns returned in both their formatted and unformatted forms, use the form "+_ALL_".

Note: Data integrity problems can occur when the data provider cannot determine which version of a column (formatted or unformatted) should be written to the data set. To prevent these problems, use the + modifier to restrict the recordset to read-only access.

How to Determine Persisted SAS Format Information

To determine a table's column metadata at run time, use the Connection object's OpenSchema method and set its query type parameter to adSchemaColumns. The returned recordset will contain the persisted SAS format information for each column, along with the defined OLE DB schema information.

How to Set the SAS Formats Property

The following sample code shows how to set the "SAS Formats" property so that the columns in the recordset will include the formatting as indicated by the "SAS Formats" value.

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

cnt.Open "Provider=sas.LocalProvider.1"
cnt.Data Source = "c:\sales"
rst.ActiveConnection = cnt
rst.Properties("SAS Formats") = "saledate=mmddyy8.,+price=dollar8.2."
rst.Open "sales", , adOpenStatic, adLockReadOnly, adCmdTableDirect

Note: The second parameter on the Open method must remain empty, as shown in the code fragment.

OLE DB Details

An application that calls a SAS provider directly through the OLE DB interfaces should use the SASFORMAT structure with the DBBINDING extensions. For information about how to perform this task, see About Format and Informat Processing with OLE DB.

Resources