Using SAS Formats When You Read Data

Goal

You want your application to use SAS formats when reading data.

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

ADO Implementation

How to Structure the Value of the "SAS Formats" Property

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 that 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]]

Here is an explanation of the form:

+, an optional modifier that exposes the column in two conditions: formatted and unformatted.
COLUMN, 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, the name of the format that you want to apply.
w, the width that you want to use with the format.
d, 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, a string column with the MMDDYY8. format applied
QUANTITY, a numeric column with no format applied
PRICE, a numeric column with no format applied
PRICE_DOLLAR8.2 as a string column with the DOLLAR8.2 format applied.

Note: The fourth column is 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

If there is no 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 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 Plus (+) 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_".

CAUTION:

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 column metadata at run time, use the Connection object's OpenSchema method and set its QueryType parameter to adSchemaColumns. The returned recordset will contain the persisted SAS format information for each column, along with the defined OLE DB schema information.

Note: For more information about displaying SAS metadata, see Displaying Metadata That Is Specific to SAS Data Sets.

Sample Code for Setting the "SAS Formats" Property

The following Visual Basic 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.

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

obRecordset.ActiveConnection = obConnection
obRecordset.Properties("SAS Formats") = "saledate=mmddyy8.,+price=dollar8.2."

' The second parameter on the Open method must remain empty.
obRecordset.Open "sales", , adOpenStatic, adLockReadOnly, adCmdTableDirect

OLE DB Implementation

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