topic Re: Mapping in DI Studio in SAS Data Management

Mapping in DI Studio

Babloo — Tue, 10 Apr 2018 13:51:38 GMT

Appreciate if someone of you help me with the best approach to create the following fields. I believe I can use extract transformation but I've to use four extract transformation(one for each variable/field) . Is there any other methods available to accomplish this?

Please note that I need to use two different datasets (F_CL_CLAIM and CLAIM) to create the following variables.

Input Table	Input Field	Mapping Condition	Output Field
F_CL_CLAIM	amt	CLAIM.CLAIM_AMT_TYPE_CD='2' and SUBTYPE_CD='2'	NVK
F_CL_CLAIM	amt	CLAIM.CLAIM_AMT_TYPE_CD='2' and SUBTYPE_CD='1'	SRK
F_CL_CLAIM	amt	CLAIM.CLAIM_AMT_TYPE_CD='3' and SUBTYPE_CD='1'	SNK
F_CL_CLAIM	amt	CLAIM.CLAIM_AMT_TYPE_CD='2' and SUBTYPE_CD='3'	TRK

Re: Mapping in DI Studio

LinusH — Tue, 10 Apr 2018 17:00:52 GMT

If you need two tables extract is not the transformation to use.

Sounds like a typical SQL Join scenario.

And you don't need a separate transformation for each column calculation...? Just define the case logic for each column in the transformation.

Re: Mapping in DI Studio

Babloo — Tue, 10 Apr 2018 17:08:37 GMT

Yes, I no need to use separate transformation for each column. I need to
create only one table which has all those four columns.

Could you please help me understand how I can use case when logic or join
transformation here?

Re: Mapping in DI Studio

LinusH — Tue, 10 Apr 2018 21:59:44 GMT

Is right there in the mappings.
You could either so the full point-and-click which is a bit cumbersome, or just write the syntax as services mapping.
See doc and/or help pages within the application for details.

Re: Mapping in DI Studio

Babloo — Wed, 11 Apr 2018 00:50:37 GMT

Are you suggesting me to use 'case when' in expression tab? If not, could
you please point me to the documentation which you wanted to convey?

Re: Mapping in DI Studio

Erik_Zencos — Wed, 11 Apr 2018 13:40:46 GMT

You can also try this code in the expression field (functions are usually most efficient):

IFN(CLAIM.CLAIM_AMT_TYPE_CD = '2' AND CLAIM.SUBTYPE_CD = '2', F_CL_CLAIM.AMT, 0)

This IFN function returns the result (AMT) if condition is true, otherwise returns 0 (or any other value you want). Remember to map all input fields to the expression (using the SQL Join).