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 |
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.
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).
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.