topic Re: Length statement not having any effect in SAS Programming

Truncation of values in column created by case statement

Sathya3 — Tue, 27 Aug 2024 16:39:01 GMT

I am working on a large data where I am creating 150+ columns using case statements in proc sql create table with multiple join conditions. One of the columns which I am creating using case statement is not getting created properly.

Values in this column which I am getting from source are getting truncated even post applying enough format and length.I am left joining main table with the same ref table multiple times but with different joining conditions.I am providing an example for more understanding.It is not the actual code I am using.

Why are the values in the new column truncated?

Example :

Proc sql;

create table out as

case when source.col1 is not null then ref.class

when source.col2 is not null then ref1.class

end as category format $100 length = 100

from source

left join table_ref ref

on source.col3=ref.col3

left join table_ref ref1

on source.col4=ref.col4

;

quit;

Re: Truncation of values in column created by case statement

ballardw — Tue, 27 Aug 2024 17:10:54 GMT

Please provide code that you have tested that duplicates the problem behavior. When you say "It is not the actual code I am using." then how do we know what may be happening.

Way better would be to provide examples of data sets, as working data step code, that actually demonstrate the behavior. Then we have a chance.

Some things to consider or share:

Provide and example of a source value that was truncated. Provide an example of what it was truncated to. Describe which variable, table and variable, this happened to.

What is the defined length of each of Ref.class and Ref1.class?

What encoding is the SAS session running? Are any of the source tables external databases? If so, which encoding do those tables store the variables as?

What does your LOG show for this process? Any warnings or notes about data values?

Re: Truncation of values in column created by case statement

ChrisNZ — Tue, 27 Aug 2024 22:48:13 GMT

Your join is wrong. You don't use ref1.

Your whole SQL is invalid (no select , etc).

Check your code, this works as expected*:

data SOURCE;
  retain COL1-COL4 1;
run;

data TABLE_REF;
  retain COL3-COL4 1;
  CLASS=repeat('a',299);
run; 

proc sql;
  create table OUT as
  select case when SOURCE.COL1 is not null then REF.CLASS
              when SOURCE.COL2 is not null then REF1.CLASS
              end as CATEGORY length=300
  from SOURCE
         left join 
       TABLE_REF ref
         on SOURCE.COL3=REF.COL3
         left join 
       TABLE_REF ref1
         on SOURCE.COL4=REF1.COL4
;
quit;

* Almost as expected. SAS Studio displays a non-existent label. 😞

Re: Truncation of values in column created by case statement

Sathya3 — Wed, 28 Aug 2024 16:10:22 GMT

An update of the above issue is when I try to create only that column or 3 or 4 columns in addition to that column , truncation is not happening. When I try to create more than 3 or 4 columns in same create table as select statement then values are truncated in that particular column..Very strange issue..and I have defined length as well while creating that column using case statement but it does not seem to have any effect..My source is hive (Hadoop) tables

Re: Truncation of values in column created by case statement

Sathya3 — Wed, 28 Aug 2024 16:11:41 GMT

I can't provide the actual code /data due to privacy policy of my organisation.

Length statement not having any effect

Sathya3 — Wed, 28 Aug 2024 16:18:06 GMT

I am creating a dataset by deriving new columns using case statement.My source is in hive (Hadoop)
Few values are getting truncated for a particular column and length statement does not seem to have any effect.I can't share the actual code /data due to privacy policy of my organisation.Presenting a sample code for understanding
Example :

Proc sql;

create table out as

case when source.col1 is not null then ref.class

when source.col2 is not null then ref1.class

end as category format $100. length = 100

from source

left join table_ref ref

on source.col3=ref.col3

left join table_ref ref1

on source.col4=ref.col4

;

quit;

Re: Length statement not having any effect

PaigeMiller — Wed, 28 Aug 2024 16:19:42 GMT

Make up some fake data that illustrates the problem.

Re: Length statement not having any effect

ballardw — Wed, 28 Aug 2024 16:33:11 GMT

I doubt there is any way we can replicate the behavior of a HADOOP related problem. Perhaps it is time to take this to SAS tech support.

Re: Length statement not having any effect

Sathya3 — Wed, 28 Aug 2024 16:50:18 GMT

How do I contact sas tech support

Re: Length statement not having any effect

PaigeMiller — Wed, 28 Aug 2024 16:58:40 GMT

support.sas.com

Re: Length statement not having any effect

Kurt_Bremser — Wed, 28 Aug 2024 17:53:53 GMT

Plese don't re-post the identical question; I merged it into the original thread.

Your code can NEVER work, the SQL misses the necessary SELECT.