SAS ODBC Driver User's Guide and Programmer's Reference

User-Specified SQL Options

This section describes two SQL options that affect how other default conversions of data types or data values can be made: Infer INTEGER from FORMATS and Support VARCHAR. A third SQL option, Fuzz Numbers at N Places, is important in comparison operations. You can specify these options in the SQL Options page of the SAS ODBC Driver Configuration dialog. (See Naming Your Data Source and Specifying SQL Options.)

Infer INTEGER from FORMAT Option

Even when no FORMAT string is specified for SAS data, the SAS System assigns a default width and number of decimal places to the data. If the SQL Option Infer INTEGER from FORMAT is selected, then the SAS ODBC driver reports SAS columns of NUM(n,0) data types as SQL_INTEGER, where n is less than 12. This can be important, because some PC products do not use indexes on floating-point columns. If those columns actually contain only integer values, then using this option enables these products to honor the index and to allow updates. See Updating Attached Tables for more information.

Support VARCHAR Option

The SQL option Support VARCHAR causes the SAS ODBC driver to report the data type CHAR(n) as SQL_VARCHAR, where n is greater than 80. Because SAS is fixed width, CHAR fields are often specified at the maximum. For example, for a list of messages the text width might be specified as 200 characters, even though the average width is much less. Reporting it as SQL_VARCHAR enables some PC products to use less memory.

Fuzz Numbers at N Places Option

This option addresses a problem that arises from the conversion of floating-point numbers. Floating-point numbers are stored in different binary representations on different computer hardware. Even when data is transferred between different applications on the same type of hardware, the precision of floating-point numbers may be affected slightly due to conversion between ASCII and binary representations.

This effect is usually so slight that it is insignificant when a number is used in calculations. For example, the numbers 65.8 and 65.799999999999 are practically identical for mathematical purposes, and the difference between them might be the result of conversion between representations rather than any purposeful change in value.

However, such a slight difference in value can keep a number from comparing correctly. For example, many ODBC applications include a WHERE clause that lists every column in a record at its current value whenever the application performs an UPDATE. This is done to ensure that the record has not been changed since the last time it was read. Sometimes a comparison may fail because of the aforementioned problem with floating-point conversion.

To solve this problem, SAS "fuzzes" numbers (standardizes the degree of precision to use, overriding the hardware-specific representations). Instead of using exact comparisons, SAS checks to make sure that the numbers are acceptably close.

By default, the degree of precision is 12 decimal places. Given a number N, then if N1 were to be checked for equality with N, the SAS ODBC driver would use the SQL BETWEEN function to determine whether N1 > (N - (ABS(N * 10**-12))) AND N1 < (N + (ABS(N * 10**-12))).

If N=0, the driver checks for BETWEEN -(10**-12) AND (10**-12).

Chapter Contents
Previous
Next
Top of Page