CODEWITHSUNDEEP
sas
Morten
Morten

Reputation: 101

Truncation when using CASE in SQL statement in SAS (Enterprise Guide)

I am trying to manipulate some text files in SAS Enterprise Guide 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?

Upvotes: 0

Views: 280

Answers (1)

Tom
Tom

Reputation: 51581

This is a known problem with using character functions inside a CASE statement. See this thread on SAS Communities https://communities.sas.com/t5/SAS-Programming/Truncation-when-using-CASE-in-SQL-statement/m-p/852137#M336855

Just use the already calculated result in the other variable instead by using the CALCULATED keyword.

CREATE TABLE WORK.Area_Z_Added AS 
SELECT 
   t1.Area
  ,t1.pedArea
  ,t1.Text
  ,(tranwrd(t1.Text,'zOffset="0"',cats('zOffset="',put(t2.Z,8.2),'"')))
    AS text21 length=1800
  ,(case when t1.type='Area'
         then calculated text21
         else t1.Text
    end) AS text2 LENGTH=1800
  ,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
;

If you don't need the extra TEXT21 variable then use the DROP= dataset option to remove it.

CREATE TABLE WORK.Area_Z_Added(drop=text21) AS ....

Upvotes: 1

Related Questions