SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Conversion of SAS code into transformation

Accepted Solution Solved
Reply
Super Contributor
Posts: 625
Accepted Solution

Conversion of SAS code into transformation

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;

 


Accepted Solutions
Solution
‎04-08-2018 05:25 AM
Super User
Posts: 5,853

Re: Conversion of SAS code into transformation

Pretty sure that there is an option for that just need to be checked.
Data never sleeps

View solution in original post


All Replies
Super User
Posts: 5,853

Re: Conversion of SAS code into transformation

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

Data never sleeps
Super Contributor
Posts: 625

Re: Conversion of SAS code into transformation

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

Super User
Posts: 5,853

Re: Conversion of SAS code into transformation

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
Super Contributor
Posts: 625

Re: Conversion of SAS code into transformation

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

Super Contributor
Posts: 625

Re: Conversion of SAS code into transformation

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;
Super User
Posts: 5,853

Re: Conversion of SAS code into transformation

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
Super Contributor
Posts: 625

Re: Conversion of SAS code into transformation

I got it. Could you please tell me how can I use 'distinct' option in join
transformation?
Solution
‎04-08-2018 05:25 AM
Super User
Posts: 5,853

Re: Conversion of SAS code into transformation

Pretty sure that there is an option for that just need to be checked.
Data never sleeps
Super Contributor
Posts: 625

Re: Conversion of SAS code into transformation

Right. I found where it is.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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