topic Re: Conversion of SAS code into transformation in SAS Data Management

Conversion of SAS code into transformation

Babloo — Thu, 05 Apr 2018 10:27:55 GMT

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;

Re: Conversion of SAS code into transformation

LinusH — Thu, 05 Apr 2018 12:20:40 GMT

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

Re: Conversion of SAS code into transformation

Babloo — Thu, 05 Apr 2018 13:07:02 GMT

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

Re: Conversion of SAS code into transformation

LinusH — Thu, 05 Apr 2018 17:11:45 GMT

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.

Re: Conversion of SAS code into transformation

Babloo — Fri, 06 Apr 2018 07:20:35 GMT

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

Re: Conversion of SAS code into transformation

Babloo — Fri, 06 Apr 2018 07:32:08 GMT

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;

Re: Conversion of SAS code into transformation

LinusH — Fri, 06 Apr 2018 11:20:17 GMT

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&docsetVersion=4.903&locale=en

Re: Conversion of SAS code into transformation

Babloo — Fri, 06 Apr 2018 11:56:37 GMT

I got it. Could you please tell me how can I use 'distinct' option in join
transformation?

Re: Conversion of SAS code into transformation

LinusH — Fri, 06 Apr 2018 15:51:06 GMT

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

Re: Conversion of SAS code into transformation

Babloo — Fri, 06 Apr 2018 17:09:37 GMT

Right. I found where it is.