Using the SQL procedure to read in a Snowflake table with the SCANSTRINGS=yes LIBNAME or data set options might not reduce the lengths of character variables when the query includes one of the following:

• DISTINCT keyword
• ORDER BY clause
• a join
• aggregate functions (MIN, MAX, SUM, COUNT, FREQ, AVG, MEAN) or the COALESCE function
• HAVING clause
• SET operator other than an OUTER UNION
• WHERE clause containing a sub-query, referencing a database management system (DBMS) table using the same libref

Instead, the length of the column remains as defined in the database or at 32,767 if the defined length is longer.

This issue can result in slower performance, and the resulting data set needs more space for storage.

A workaround is to use explicit pass-through with the SQL procedure.

Click the Hot Fix tab in this note to access the hot fix for this issue. 

Note: The hot fix addresses issues when the Snowflake table contains only one column. See SAS Note 71029 for issues when there is more than one column in the table. In addition, see SAS KB0042067 for issues where column lengths might not be shortened when renaming columns with the SQL procedure.

Operating System and Release Information

A fix for this issue for SAS/ACCESS Interface to Snowflake 9.43 is available at:

A fix for this issue for SAS/ACCESS Interface to Snowflake 9.42 is available at: