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;
Could you please help me understand to incorporate subquery in SQL join transformation?
The inner join creates a cartesian product, the subquery only sets the where condition to true and does not multiply observations.
So with the inner join you get an observation for every claim to a policy, while with the subqery you get only one observation per policy if at least one claim exists.
The inner join creates a cartesian product, the subquery only sets the where condition to true and does not multiply observations.
So with the inner join you get an observation for every claim to a policy, while with the subqery you get only one observation per policy if at least one claim exists.
Could you please help me understand to incorporate subquery in SQL join transformation in DI studio? I looked at this document
but it was not helpful.
Guide someone with a GUI via test messages is hard.
If you can't fulfill your task based on the documentation, you need the means of interactive knowledge sharing.
Have you as a start taken the DI Studio training?
A part from that, I guess some sort of on site support from some consultant is the most appropriate way forward.
DI Studio is a great tool, if you know how to use it. Sounds like you are alone, and I think that a single user environment is not the best use case for DIS. The overhead by defining metadata, and understanding and structuring this abstraction layer will not repay if the solution is small.
@Babloo wrote:
Could you please help me understand to incorporate subquery in SQL join transformation in DI studio? I looked at this document
but it was not helpful.
I could not think of anything anybody here could tell you in excess of that formidable documentation page. If that does not work for you, you need some serious onsite training with someone who can literally guide your hand.
And knowing from experience how important working from SAS documentation is, I ask you to question yourself if working with SAS is a field of work you are suited for.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.