topic Re: When will we create a new column manually in a SAS DI- Table loader transformation in SAS Data Management

When will we create a new column manually in a SAS DI- Table loader transformation

VVDR — Wed, 04 Sep 2019 02:13:30 GMT

Hi all,

I am using SAS DI user written transformation and I have the following doubts. Please suggest to gain some knowledge.

I am using an existing user written transformation.

In that transformation, in the existing Data step block I am manually writing a code and creating one new column X.

That column loads the data into the next level crated table ABC.

what all steps I need to run in terms of executing it successfully.

1. In the mapping section, do I need to right click --> and create a new column X.

2. Map it to the ABC column manually as X is an existing column already.

I am very much interested in this topic here. Please help me in understanding if it is correct way of dealing while creating a new column.

Re: When will we create a new column manually in a SAS DI- Table loader transformation

Patrick — Wed, 04 Sep 2019 02:48:05 GMT

@VVDR

You can do whatever you like with user written code so it's sort of hard to answer your question.

If you don't use any of the metadata based macro variables that DIS generates in your user written code then the code won't depend on anything you do in SAS Metadata. In such a case you just want to have a target metadata table that's in sync with the physical table that your user written code generates.

It still doesn't hurt if you do the source to target mapping - not because this will affect what gets executed but because it will support metadata based lineage and impact analysis.

I personally would as a bare minimum at least use the DIS generated macro variables for the source and target tables in your user written code (&_input, &_output). Have a look into the code tab and you'll see what gets generated and is available.

Re: When will we create a new column manually in a SAS DI- Table loader transformation

VVDR — Wed, 04 Sep 2019 11:30:41 GMT

The other way of asking this question-- Can the user written transformation generate a new column so that it can be mapped to the target table columns.

Please advise.

Regards

Rams129

Re: When will we create a new column manually in a SAS DI- Table loader transformation

LinusH — Wed, 04 Sep 2019 11:51:52 GMT

Quick answer - no.

The main difference between User Written code and User Written transformation is that the later logic can be reused. There is no automatic populaitn of tnew arget column names.

Re: When will we create a new column manually in a SAS DI- Table loader transformation

ErikLund_Jensen — Wed, 04 Sep 2019 16:39:50 GMT

Hi @VVDR

I always do the following:

Write the code using &_INPUT and &_OUTPUT as data set names in the code.
Update the output table's metadata after succesful execution.
Fix the mappings to secure lineage and column impact analysis.

Tip:

A user written transformation often do complicated tings (otherwise a standard transformation is preferred), so a new output variable is a result of operations on several input columns. You cannot map more than one input column to an output column, but it can be solved with an expression on the output column like %dummy(inputcol1,inputcol2,inputcol3). The macro is never executed and is just a way of forcing mapping n:1, so there is no need for an actual %dummy macro in a macro library.

Re: When will we create a new column manually in a SAS DI- Table loader transformation

ErikLund_Jensen — Wed, 04 Sep 2019 16:42:58 GMT

Hi @VVDR

Tips continued:

All permanent tables read or written by the transformation code should be on the canvas and connected to the transformation. Otherwise the lineage is lost.