User-Specified SQL Options

This option affects how other default conversions of data types or data values can be made. Even when no format string is specified for SAS data, SAS 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 type as SQL_INTEGER, where n is less than 12. This can be important because some software products do not use indexes on floating-point columns. If those columns actually contain only integer values, then using this SQL option enables these products to read the index and allow updates. For more information, see Updating Attached Tables.

This option affects how other default conversions of data types or data values can be made. Enabling this option 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 value. For example, for a list of messages, the text width might be specified as 200 characters, even though the average width of a message is much less. Reporting the data type as SQL_VARCHAR enables some software products to use less memory.

Sometimes, the _0 string occurs at the end of names in SQL queries constructed by certain applications. Enabling this SQL option prevents a SAS error by specifying that the SAS ODBC Driver keeps the _0 string at the end of a table name. For example, the string _0 is added to table aliases in SQL queries that Microsoft Query constructs so that the table name mytable becomes mytable_0. This table name is nine characters in length, which Version 6 of the SAS System cannot handle because table names are limited to eight characters.

To prevent a SAS error, the SAS ODBC Driver removes the _0 string in the eighth or greater position of a table name. However, if SAS variable names contain the _0 string at the eighth or greater position, a query might not execute properly. A variable name such as aaaaaa_0 does not cause a SAS error, but variable names such as aaaaaaa_0 and aaaaaaaa_0 do cause SAS errors. The SQL option Disable _0 override parsing prevents the SAS ODBC Driver from removing the _0 string.

This SQL option addresses a problem that occurs during 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 might be affected slightly because of the 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 almost identical for mathematical purposes. 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 issues an UPDATE statement. This is done to ensure that the record has not changed since the last time it was read. Sometimes, a comparison might fail because of the 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, if N1 were to be checked for equality with N, then the SAS ODBC Driver would use the SQL BETWEEN function to determine the following:

N1 > (N - (ABS(N * 10**-12))) AND N1  (N + (ABS(N * 10**-12)))

If N=0, the SAS ODBC Driver checks for the following:

BETWEEN -(10**-12) AND (10**-12)