|SAS ODBC Driver User's Guide and Programmer's Reference|
section describes two SQL options that affect how other default conversions
of data types or data values can be made:
Infer INTEGER from FORMATS
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
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))).
N=0, the driver checks for
BETWEEN -(10**-12) AND (10**-12).
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.