Using SAS Informats When You Write Data

Goal

You want your application to use SAS informats when writing data.

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

ADO Implementation

How to Structure the Value of the 'SAS Informats' Property

SAS implements a set of informats that can be used to transform formatted character values into basic character and numeric values. Informats are controlled with the SAS Informats customized Recordset property.

This property takes a string value that specifies the informat that you want to use. The string value also lists one or more columns to which it should be applied. Use the following form to enter the string value:

COLUMN[=INFORMAT[w].[d]]

Here is an explanation of the form:

COLUMN, the name of a column in the recordset (or variable in the data set). (If you need to specify more than one column, you must separate the column names with commas.)
INFORMAT, the name of the informat that you want to apply
w, the width that you want to use with the informat
d, the number of decimal places (if any) to include with numeric informats

For example, the following informat string value removes the dollar sign and commas from the values in a column named amount. For an input value of $1,000,000, the result is a numeric value of 1000000.

amount=DOLLAR11.

How Default Informats Are Applied

If there is no explicit informat information (or if you specify "_ALL_" as the "SAS Informats" property value), the IOM and SAS/SHARE providers apply informat properties to all columns according to the following rules:

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

Note: The "SAS Informats" property does not support use of the plus sign (+) modifier with the "_ALL_" keyword. This modifier can be used with the "SAS Formats" property to expose data as two columns: one formatted and one unformatted.

How the Use of Informats Affects Formatting

Whenever an informat is applied to a column, the SAS providers also apply format properties to that column. If a format is not explicitly specified through the use of the "SAS Formats" property, then the provider that is being used in the application applies the column's default format. (For more information about using formats, see Using SAS Formats When You Read Data .)

The following Visual Basic code uses a simple SAS data set named people that contains two columns: name and birthday. The code specifies an informat for the birthday column, which is sufficient for the task of adding a few new records to the data set.

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

obRecordset.ActiveConnection = obConnection
obRecordset.Properties("SAS Informats") = "birthday=MMDDYY8."

' The second parameter on the Open method must remain empty.
obRecordset.Open "people", , adOpenStatic, adLockPessimistic, adCmdTableDirect 
obRecordset.AddNew Array("name", "birthday"), Array("Beth", "02/13/73")
obRecordset.AddNew Array("name", "birthday"), Array("David", "05/01/65")
obRecordset.Close

Because the "SAS Formats" property is not used to explicitly specify a format for birthday, the column's default format is used. Because this code only writes data, the applied format does not affect the results. For applications that read and write records, you must ensure that each column uses the appropriate informat and format.

In the following Visual Basic code, the first statement applies a format to the birthday column. The second statement applies a complementary informat to the birthday column.

obRecordset.Properties("SAS Formats") = "birthday=MMDDYY8."
obRecordset.Properties("SAS Informats") = "birthday=MMDDYY8."

By contrast, in the following code, the property settings for birthday are not complementary. The format (DOLLAR9.2) implies that the underlying numeric value is monetary, but the informat (MMDDYY8.) implies that the underlying value is a SAS date.

obRecordset.Properties("SAS Formats") = "birthday=DOLLAR9.2"
obRecordset.Properties("SAS Informats") = "birthday=MMDDYY8."

Even though this pairing of format and informat is not logical, the providers will not prohibit you from entering this type of configuration. You must make reasonable choices.

Note: As with informats, you can use the "_ALL_" keyword to specify that all columns use either system default formats or persisted formats.

How to Determine Persisted SAS Informat Information

To determine column metadata at run time, use the Connection object OpenSchema method and set its QueryType parameter to adSchemaColumns. The returned recordset will contain the persisted SAS informat 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.

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.