BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Babloo
Rhodochrosite | Level 12

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
Pretty sure that there is an option for that just need to be checked.
Data never sleeps

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20

SQL Join transformation (where you could use sub-query, or simpler just an inner join).

Data never sleeps
Babloo
Rhodochrosite | Level 12

How can I use inner join instead of sub-query?

LinusH
Tourmaline | Level 20
Not sure what you mean, but an inner join would give the same result as your subquery. And simpler to implement in SQL Join transformation.
Data never sleeps
Babloo
Rhodochrosite | Level 12

Could you please help me with the skeleton to achieve this in SQL transformation using inner join?  

Babloo
Rhodochrosite | Level 12

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;
LinusH
Tourmaline | Level 20

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...

http://documentation.sas.com/?docsetId=etlug&docsetTarget=p0tpbt07h7iy7qn1071lwlx2u2sh.htm&docsetVer...

Data never sleeps
Babloo
Rhodochrosite | Level 12
I got it. Could you please tell me how can I use 'distinct' option in join
transformation?
LinusH
Tourmaline | Level 20
Pretty sure that there is an option for that just need to be checked.
Data never sleeps
Babloo
Rhodochrosite | Level 12
Right. I found where it is.

sas-innovate-2024.png

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.

 

Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 9 replies
  • 1069 views
  • 4 likes
  • 2 in conversation