The lengths of the VARCHAR data type columns in a Snowflake table might not be reduced when the following are true:

• You run SAS^® 9.4M7 (TS1M7) with Hot Fix L2C008, SAS^® 9.4M8 (TS1M8) with Hot Fix L9B007, or the SAS^® Viya^® Platform Stable 2024.08 or a later version.
• You use the SCANSTRINGCOLUMNS=YES LIBNAME or DATA step option.
• You use the SQL procedure, and your query contains at least 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
• Your Snowflake table contains more than one column.

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

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

See SAS Note 70901 for the original issue with the SCANSTRINGCOLUMNS option not reducing the length. In addition, see SAS KB0042067 for issues where column lengths might not be shortened when renaming columns with the SQL procedure.

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

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: