Columns with an unexpected length can occur because the SQL procedure might ignore the LENGTH column modifier


The LENGTH column modifier might be ignored in PROC SQL when using the DBSERVER_MAX_BYTES=1 LIBNAME option.

For example, if a database column contains a length of 8, then the following SELECT clause would be expected to result in a column with a length of 2. However, a column with a length of 8 occurs instead.

LIBNAME libref ORACLE PATH="path" USER=user PASSWORD=password DBCLIENT_MAX_BYTES=1 DBSERVER_MAX_BYTES=1;
PROC SQL;
CREATE TABLE work.table-name AS
SELECT oracle-column-name AS alias-name LENGTH=2
FROM libref.oracle-table;
quit;

This behavior is likely to occur in SAS® Data Integration Studio jobs where column mappings display a warning in the GUI, and the generated code writes a warning message similar to the following:

WARNING: Mapping of the target column alias-name is too short for the specified source column oracle-column-name. Values will be truncated.

A workaround for this issue is to explicitly truncate the column. For example, you could change the SELECT clause above to the following:

SELECT SUBSTR(oracle-column-name,1,2) AS alias-name LENGTH=2