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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.