In this recipe, you learn how to prepare the IOM and SAS/SHARE data providers to process user-written formats and informats. Sample code for the ADO recipe is included.
| Applies to: | SAS IOM Data Provider, SAS/SHARE Data Provider |
| Implement using: | ADO or OLE DB |
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 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]]
Following is an explanation of the form:
For example, the following informat string value removes the dollar sign and commas from the value $1,000,000 in a column named amount. The result is the numeric value 1000000.
amount=DOLLAR11.
The following sections provide more details:
In the absence of 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:
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.
Whenever an informat is applied to a column, the IOM and SAS/SHARE 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 current SAS provider applies the column's default format. (For more information about using formats, see Using Formats When You Read Data.)
If your application is only writing data, then the default format is inconsequential. However, if your application is reading and writing data using the same recordset, then you need to be aware of how formats are paired with informats. The following example uses a simple table (data set) named people that contains two columns (variables): name and birthday.
obRecordset.ActiveConnection = obOpenConnection
obRecordset.Properties("SAS Informats") = "birthday=MMDDYY8."
obRecordset.Open "people", , adOpenStatic, adLockPessimistic, adCmdTableDirect
obRecordset.AddNew Array("name", "birthday"), Array("Beth", "02/13/73v)
obRecordset.AddNew Array("name", "birthday"), Array("David", "05/01/65")
obRecordset.Close
The above code fragment specifies an informat for the birthday column, which is sufficient for the task of adding a few new records to the data set. The birthday column is also assigned a format, even though one is not explicitly specified. However, because you are only writing data, the default format assigned to the birthday column is inconsequential.
For applications that are designed to read and update records, you must ensure that each column uses the appropriate informat and format. Consider the following example:
obRecordset.Properties("SAS Formats") = "birthday=MMDDYY8."
obRecordset.Properties("SAS Informats") = "birthday=MMDDYY8."
The first statement applies a format to the birthday column; the second applies a complementary informat to the birthday column. In the statements below, the property settings for birthday are not complementary because 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 is not a sensible pairing of format and informat, the IOM and SAS/SHARE providers will not prohibit you from entering this kind of configuration; it is up to you, as the application developer, to 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.
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 informat information for each column, along with the defined OLE DB schema information.
An application calling a SAS provider directly through the OLE DB interfaces should use the SASFORMAT structure with the DBBINDING extensions. Details can be found in About Format and Informat Processing with OLE DB.