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 drivers report 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 drivers 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 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. Disabling this option might be helpful if you need to access data on a Version 6 SAS/SHARE server because table names are limited to eight characters in Version 6.

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 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 driver checks for the following:

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