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;
SQL Join transformation (where you could use sub-query, or simpler just an inner join).
How can I use inner join instead of sub-query?
Could you please help me with the skeleton to achieve this in SQL transformation using inner join?
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;
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...
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.