Truncation of values in column created by case statement

[Sathya3]

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;

[ballardw]

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?

[Sathya3]

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

[Sathya3]

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

[Sathya3]

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;

[PaigeMiller]

Make up some fake data that illustrates the problem.

[ballardw]

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.

[Sathya3]

How do I contact sas tech support

[PaigeMiller]

support.sas.com

[Kurt_Bremser]

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.

[ChrisNZ]

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. 😞