Could someone clarify the effect of using different conditions, such as "greater," "smaller," or "not equal," when joining tables? I initially thought that an inner join requires an equal condition, but now I see that other conditions are possible. How does selecting "not equal" instead of "equal" impact the inner join?
PROC SQL; CREATE TABLE WORK.Non_Preg_Count AS SELECT t1.CIN AS CIN1, t1.New_Month AS New_Month1, t2.CIN AS CIN2, t2.New_Month AS New_Month2 FROM WORK.MERGED_FINAL t1 INNER JOIN WORK.MERGED_PREG_FINAL t2 ON (t1.CIN ^= t2.CIN); QUIT;
The INNER JOIN means that both sides have to contribute to the join by matching based on the ON criteria.
The use of NOT EQUAL operator as part of the logic of the ON condition just means that the test that needs to succeed for the combination to be considered a match is that the two values being compared are not equal.
Thanks @Tom , but I am not sure if I get your point. In the picture I shared, the ON condition is based on CIN i.e. t1.CIN1 = t2.CIN2. So, it won't make sense to me to have t1.CIN1=t2.CIN2 in my INNER JOIN and also t1.CIN1 ^= t2.CIN2 as another condition. Am I missing something here?
It's a good question, but I think you've got yourself into a bit of a muddle. I explain a SQL join as "First, all of the records are matched with all of the records (a cartesian join)." "Second, based on your selection criteria, any records that don't match the criteria are removed". So, suppose t1.CIN values are a, c, e and t2.CIN values are b, c, e. After step one, you'll have 9 records with keys a, b; a, c; a, e; c, b; c, c; c, e; e, b; e, c; e, e. Now, step 2, follow the selection criteria. This will remove records c, c and e, e leaving you with 7 records.
Tom
Thanks @TomKari I am getting somewhere now. So, in this case, if I want to pull the CINs that are NOT in t2 table how should I proceed? should I just use Inner Join with t1.CIN ^= t2.CIN?
I am trying to get this with SAS EG:
ELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null
Try this:
proc sql;
create table Want AS
select T1.*
from WORK.MERGED_FINAL t1
left join WORK.MERGED_PREG_FINAL t2
on t1.CIN = t2.CIN
where missing(t2.CIN)
;
quit;
@ralizadeh wrote (bold italics mine)
Thanks @Tom , but I am not sure if I get your point. In the picture I shared, the ON condition is based on CIN i.e. t1.CIN1 = t2.CIN2. So, it won't make sense to me to have t1.CIN1=t2.CIN2 in my INNER JOIN and also t1.CIN1 ^= t2.CIN2 as another condition. Am I missing something here?
I don't see any instance of t1.cin=t2.cin in your picture (reproduced below) or in the associated code box. BTW, you typed t1.cin1=t2.cin2 - I presume you meant t1.cin=t2.cin.
SQL can be a little weird, especially when you're using something like EG, which tries to give you a user-friendly interface. It might be easier for you to do some experimenting, and see what happens. I suggest you try these as t1 and t2, and see what happens. Feel free to post questions. It'll give everybody a basis for discussion.
T!:
Recnum1 CIN 101 a 102 (null) 103 a 104 c 105 e 106 (null) 107 f 108 f 109 g
T2:
Recnum2 CIN 201 (null) 202 a 203 a 204 b 205 b 206 c 207 c 208 (null) 209 g
@ralizadeh wrote:
Thanks @Tom , but I am not sure if I get your point. In the picture I shared, the ON condition is based on CIN i.e. t1.CIN1 = t2.CIN2. So, it won't make sense to me to have t1.CIN1=t2.CIN2 in my INNER JOIN and also t1.CIN1 ^= t2.CIN2 as another condition. Am I missing something here?
I don't understand what you are asking. That GUI tool looks like it is used to generate the SQL by letting your point and click. So if you select the = operator it generates code with the = operator. If you select the ^= operator it generates code with the ^= operator. Note that the ^ is just short hand for NOT. In SAS code you can also just the pneumonic NE for not equal in the code.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.