Multiple lengths were specified for the variable in SAS DI STUDIO

[farshidowrang]

Dear friends,

I have many tables connected to a LOOKUP transformation. There is a character variable (let's call it X) which has different length in the tables. I want all of them be finally $5.

I wrote:

put(X , $5.)

in the EXPRESSION for the variable X in the LOOKUP transformation but it didn't solve the problem.

Do you know how I can solve this?

Best regards

Farshid Owrang

[RichardDeVen]

Is the issue that the X column is longer or shorter than $5 ?

Specify the variable length prior to a SET statement that reads a table with a column named X, or before any other computation that might implicitly set the variables length.  By doing so, the code forces the variable length, not side effects.

data want;
  length x $5;
...
* set statement;
* formula statement;
run;

[farshidowrang]

Thank you very much my friend!

[ChrisNZ]

Is this a data step? I assume so since this message is not displayed by SQL afaik.

If you set the length to 5, and some of the variables (not the values, SAS does not check the values) are longer than 5, th message is displayed because the variables with a length greater than 5 might be truncated to 5, and therefore lose data.

Running

X=put(X,$5.);

does not reset the length of the variable, only the length of the value.

Running

Y=put(X,$5.);

drop X;

rename Y=X;

should do what you want.

[farshidowrang]

Thank you very much!

[Patrick · Accepted solution]

The expression gets used for the target table and not for source tables.

Implementing ETL processes using DIS the same named variable should always mean the same and have the same attributes in all tables so best way would be to fix the source problem. You can use Tool/Standardize Columns for this task. Once metadata is fixed you also need to re-create permanent physical tables for such changes to take effect. And you need to redeploy all DIS jobs using any of the tables with changed metadata.

The Lookup Transformation doesn't let you change input variable attributes. If it isn't possible for you to fix the source problem then one way to go is to add a SQL Extract node where you create the variable as required, define the target as view and then use this view as input to the Lookup Transformation.

[farshidowrang]

Perfect!

Thank you very much!