topic Re: Append datasets with filters in one transformation in SAS Data Management

Append datasets with filters in one transformation

David_Billa — Sun, 30 Aug 2020 16:19:52 GMT

My objective is to append the source datasets in one of the DI Job. I could do this with the User Written code Transformation and log also confirmed the required Output rows. But when I tried to open the dataset, it's not opening due to the warning 'A mapping with multiple sourcr columns requires a expression'.

I would like to seek help to handle mapping in the target dataset while appending the four datasets which had similar structure. Without mapping from to target it's not producing any rows. I would like to achive this in one step

Code which I have in User Written code Transformation is,

data ALLOC_EXP_A(drop=Expense Paid Address)
) ;
set Alloc_Expns 
Commns_Alloc_Expns 
Attri_Acqs_Alloc_Expns 
Alloc_Expns;
VALUE_LC=Expense;
VALUE_TC=Expense;
run;

@Rama_V @Patrick @PaigeMiller @Jagadishkatam

Re: Mapping while append the datasets with SET statement

David_Billa — Sun, 30 Aug 2020 11:04:13 GMT

Calling @Patrick to seek his input

Re: Append datasets with filters in one transformation

Rama_V — Mon, 31 Aug 2020 01:20:37 GMT

User Written Transformation creates a work table and they are referred by auto generated macro variables. for example _input1 , _output1 they can be used to refer input and output table connected to the transformation.

Write the below code with &_output1. and execute of the code. If the target table doesn't have metadata columns then it needs to updated. It is done by right clicking on the target table then select update metadata. If the option is not shown try after saving the job.This should populate metadata columns from the physical data set referred by the target table.

data &_output1.;
set Alloc_Expns 
Commns_Alloc_Expns 
Attri_Acqs_Alloc_Expns 
Alloc_Expns;
VALUE_LC=Expense;
VALUE_TC=Expense;
run;

Have you tried Append Transformation, which does same job and can visualize the columns

Re: Append datasets with filters in one transformation

David_Billa — Mon, 31 Aug 2020 02:49:44 GMT

@Rama_V How to handle the mapping if I use User Written Code Transformation as we have more than one source dataset to map to the target? I already tried what you said in User Written Code Transformation but I got warning 'A mapping with multiple source columns requires a expression'. in the mapping section.

Re: Append datasets with filters in one transformation

Rama_V — Mon, 31 Aug 2020 04:00:47 GMT

Other than user written transformation, most of the transformations are auto generated code, means when you map columns and write expression in the mapping section the code is generated in the code section accordingly.

For User written, the expression is written explicitly in the code section, that means you do not need an expression in the mapping but mapping single target column with two source columns gives warning. You can ignore or to avoid this warning, just propagate the columns you need. Mapping is not required from source to target columns

If this is confusing, use Append Transformation.