When used in an SQL procedure step and the first argument is a numeric column or expression, the SUBSTRN and KSUBSTRN functions incorrectly return a numeric result with a value of 0.

The SUBSTRN and KSUBSTRN functions are unique because they accept either a character or numeric argument as their first argument. When you use these functions in a DATA step, the following occurs:

• These functions automatically convert a numeric argument to a character string using the BEST32. format.
• They remove leading and trailing spaces.
• They perform the sub-string on that converted value, which results in a character value as output. 

In this scenario, no messages are written to the SAS log to indicate that the automatic numeric-to-character conversion occurs.

However, when you use these functions in PROC SQL, the numeric result is a value of 0. No indication is given that the result is not what was expected.

Example

The code below demonstrates the different results when using SUBSTRN in a DATA step and PROC SQL. The expected result is a character variable with a value of 34:

Here's the output:

Workaround

A workaround is to revert to the SUBSTR function, and explicitly convert the number to a character argument. You can modify the FORMAT length of 32 in this example to accommodate the length of the value and/or you can use the optional LENGTH specification to control the length of the result:

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 Base SAS 9.4_M8 is available at:

A fix for this issue for Base SAS 9.4_M7 is available at: