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

Inner Join vs subquery

Accepted Solution Solved
Reply
Super Contributor
Posts: 625
Accepted Solution

Inner Join vs subquery

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?


Accepted Solutions
Solution
‎04-08-2018 05:26 AM
Super User
Posts: 9,886

Re: Inner Join vs subquery

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎04-08-2018 05:26 AM
Super User
Posts: 9,886

Re: Inner Join vs subquery

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 625

Re: Inner Join vs subquery

Posted in reply to KurtBremser

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.

 

http://support.sas.com/documentation/cdl/en/etlug/66819/HTML/default/viewer.htm#p0tpbt07h7iy7qn1071l...

 

Super User
Posts: 5,850

Re: Inner Join vs subquery

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.

Data never sleeps
Super User
Posts: 9,886

Re: Inner Join vs subquery


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

 

http://support.sas.com/documentation/cdl/en/etlug/66819/HTML/default/viewer.htm#p0tpbt07h7iy7qn1071l...

 


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 176 views
  • 2 likes
  • 3 in conversation