How to Do An Inner Join On Distinct Observations ?

[Midi]

Hi Everyone.

So , I want to do an Inner Join on distinct observations of other columns , the distinct would be performed on Division_ID from both B & C, but i'm not sure how i can do that , this is what i tried to do , and that obviously gave me an Error.

proc sql;
Create Table INDICATORS as
select A.*, B.MAT, B.ART, B.MAC, C.ID_F as F_ID
from Cust A
inner join (Select DISTINCT DIVISION_ID
      FROM ODS.R_M )B
 on A.Division_ID = B.Division_ID
inner join (SELECT DISTINCT DIVISION_ID
       FROM ODS.Fam ) C
 on A.Division_ID = C.division_ID
;
Quit; 

Any Suggestion Would Be Much Appreciated , Thank you

[Panagiotis]

At a quick glance, your inline views are the issue. You are selecting the distinct Division_ID but not not selecting any other columns, but you are referring to other columns in the SELECT clause from those in-line views.

For example,

You select B.MAT from the main SELECT clause, which i'm guessing you want from the in-line view below

(Select DISTINCT DIVISION_ID      FROM ODS.R_M) B

However, you are not selecting the MAT column in the in-line view above, but only the distinct Division_ID is being returned. There is no B.MAT column to select.

Without seeing the data it's hard to give you a solution for your specific problem. But you might want to include the columns you are selecting in the main SELECT clause in your in-line views. Here is a new in-line view B that should work:

(Select DISTINCT DIVISION_ID, MAT, ART, MAC FROM ODS.R_M) B

Now this will give you the unique combinations of Division_ID, MAT, ART and MAC. Not sure if that is what you are looking for. It might be easier to create separate views for those in-line views in their own queries, make sure those views are what you need, then use those to join. Example data would help.

- Peter