topic Re: Multiple lengths were specified for the variable in SAS DI STUDIO in SAS Programming

Multiple lengths were specified for the variable in SAS DI STUDIO

farshidowrang — Mon, 30 Mar 2020 15:15:25 GMT

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

Re: Multiple lengths were specified for the variable in SAS DI STUDIO

RichardDeVen — Mon, 30 Mar 2020 18:29:19 GMT

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;

Re: Multiple lengths were specified for the variable in SAS DI STUDIO

ChrisNZ — Tue, 31 Mar 2020 03:32:47 GMT

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.

Re: Multiple lengths were specified for the variable in SAS DI STUDIO

Patrick — Tue, 31 Mar 2020 06:04:07 GMT

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.

Re: Multiple lengths were specified for the variable in SAS DI STUDIO

farshidowrang — Tue, 31 Mar 2020 09:23:02 GMT

Thank you very much my friend!

Re: Multiple lengths were specified for the variable in SAS DI STUDIO

farshidowrang — Tue, 31 Mar 2020 09:23:36 GMT

Thank you very much!

Re: Multiple lengths were specified for the variable in SAS DI STUDIO

farshidowrang — Tue, 31 Mar 2020 09:23:55 GMT

Perfect!

Thank you very much!