04-05-2018 06:27 AM
Appreciate if someone of you help me understand to know how can I convert the following SAS code into transformations (without user-written) in DI studio? My objective is to create the variables khire, sskbt and avk.
proc sql; create table khire as Select policy_id as khiref from DECOM.d_pol_policy where policy_sk in (Select policy_sk from DECOM.f_clm_claim); create table sskbt as select claim_handling_type_cd as sskbty from DECOM.d_clm_claim_unit where claim_unit_sk in (Select claim_unit_sk from DECOM.f_clm_claim); create table avk as Select part_cd as avk from DECOM.D_CLM_CLAIM_AMT_TYPE where claim_amt_type_sk in (Select claim_amt_type_sk from DECOM.f_clm_claim); quit;
04-05-2018 01:11 PM
04-06-2018 03:32 AM
Following original query yields 5021 records.
proc sql; Select policy_id as khiref from DEXTCO.d_pol_policy where policy_sk in (Select policy_sk from DEXTCO.f_clm_claim); quit;
whereas If I use SQL join transformation using inner join, I receive 86804 records. Code of my SQL join transformation is
proc sql; create table work.W6ZV5GFE as select D_POL_POLICY.POLICY_ID as KHIREF length = 32 label = 'Policy id' from dextco.F_CLM_CLAIM, dextco.D_POL_POLICY where (F_CLM_CLAIM.POLICY_SK = D_POL_POLICY.POLICY_SK) ; quit;
04-06-2018 07:20 AM
Ok, it seems that policy_sk is not unique in DEXTCO.f_clm_claim, so there will be a M-M or 1-M join, and therefore yielding duplicates.
Since I don't know the data, I can't really tell what's the best solution (and you have also to verify my guess).
One way is first to remove duplicates, perhaps via a select distinct, or select distinct in the join itself.
Or, keep the sub-query logic. A bit trickier in SQL Join transformation, see the documentation for guidance. Be prepared for some trial and error development...
Need further help from the community? Please ask a new question.