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 April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.