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
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.
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;
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.