Truncation when using CASE in SQL statement

[MortenAgerlin]

I am trying to manipulate some text files and load them line by line in a character variable "text" which gets the length 1677 characters.

I can use the Tranwrd() function to create a new variable text21 on this variable and get the desired result as shown below. 

But if I try to put some conditions on the execution of exactly the same Tranwrd() to form the variable text2 (as shown below) it goes wrong as the text in the variable is now truncated to around 200 characters, even though the text2 variable has the length 1800 characters:

PROC SQL;
   CREATE TABLE WORK.Area_Z_Added AS 
   SELECT t1.Area, 
          t1.pedArea, 
          t1.Text, 
          /* text21 */
            ( tranwrd(t1.Text,'zOffset="0"',compress('zOffset="'||put(t2.Z,8.2)||'"'))) LENGTH=1800 AS text21, 
          /* text2 */
            (case when t1.type='Area' then
             tranwrd(t1.Text,'zOffset="0"',compress('zOffset="'||put(t2.Z,8.2)||'"'))
            else
             t1.Text
            end) LENGTH=1800 AS text2, 
          t1.Type, 
          t1.id, 
          t1.x, 
          t1.y, 
          t2.Z
      FROM WORK.VISSIM_IND t1
           LEFT JOIN WORK.AREA_Z t2 ON (t1.Type = t2.Type) AND (t1.Area = t2.Area)
      ORDER BY t1.id;
QUIT;

Anybody got a clue?

The resulting dataset is attached.

[FreelanceReinh]

Hello @MortenAgerlin and welcome to the SAS Support Communities!

This is a serious limitation of certain SAS character functions when used in CASE expressions or nested in other function calls in PROC SQL. It appears that the result of the TRANWRD call in the CASE expression is written to a buffer of length 200, hence at risk of truncation. I searched the Communities archive and found the 2017 discussion Case statement trims length to 200? where Tom suggested a workaround, which in your example translates to

case when t1.type='Area' then calculated text21 else ...

You may want to upvote ChrisNZ's corresponding SASware ballot suggestion Length returned by PROC SQL's case statement should be as defined.

[ErikLund_Jensen]

Hi @MortenAgerlin 

As @FreelanceReinh suggests, this happens because most string functions i SAS, especially functions like prxchange, tranword etc, which might change the length of a string, assigns a default length of 200 to the returned result from the function, if the returned variable is created by the function. If the returned value is assigned to an existing variable, e.g. created with a Length Statement, the length of the variable is not changed.  

A single function behaves the same way in Proc SQL and Data Steps, but there is a difference behind the scene if functions are nested. In a Data Step, the set of nested functions seems to executed as a single unit, so the given length applies to all function results in the nesting, but in Proc SQL there are "invisible" intermediate results which defaults to 200, so only the result from the outermost function is assigned the specified length.

The following code demonstrates the behaviour, and the last example also demonstrates a workaround, so the intermediate results can be explicitly declared and assigned a length and also be dropped from output, so only the final result is kept. 

data have;
  length Text $512;
  Text = 
'This is a long text to be used as an example of how Proc SQL can return unexpected results from SAS' || 
' function calls if functions are nested in a SQL clause. This happens because only the result from ' ||
' the outermost function can be assigned an explicit length, while results from embedded functions ' ||
' are assigned a default length of 200 bytes regardless of the length of argument strings.';
run;

data want0;
  set have;
  length ChangedText $512;
  ChangedText = propcase(tranwrd(Text,'SAS','sas'));
run;

proc sql;
  create table want1 as 
    select
      Text,
      propcase(tranwrd(Text,'SAS','sas')) as ChangedText length=512
    from have;
quit;

proc sql;
  create table want2 (drop=tmp) as 
    select
      Text,
      tranwrd(Text,'SAS','sas') as tmp length=512,
      propcase(calculated tmp) as ChangedText length=512
    from have;
quit;