Thank You guys for responses. @Kurt_Bremser, @LinusH, @ChrisNZ, and @ChrisHemedingerour Community Manager. It is good to hear/see that pandemic did not kill everyone. Thank You @ChrisHemedinger for the link to SAS Documentation. It is always the best foundation. I have not found there anything sugesting that outer joins are restricted to max two tables. Venn diagram is a vizualization of kind of joins as You mantioned in your post. And also thanks for the wideo about merge vs sql join is great review and I recommend it to everyone. (I could hardly focus on the content but finnaly I made it ) I think @DominiqueWeatherspoon should mention about that SQL JOINS also have a ‘control processing’ with some proc sql options like magic=101, magic=102 magic=103. Lets get back to the main topic. I will start from the beggining. If someone is not familiar what is going on please read all of the earlier information posted. My question is based on few excerpts from two books published by SAS. The first book is: PROC SQL, 3rd Edition by Kirk Paul Lafler Published by SAS Institute, 2019 And the second book is: SAS® Certification Prep Guide Advanced Programming for SAS®9 Fourth Edition In this topic we will try only talk about HORIZONTAL JOINS. OUTER JOIN(left, right, full) is a part of the horizontal join and INNER JOIN is also a horizontal join. In the first book author mentioned two times about OUTER JOINS like And the Certification Book: Creating an Inner Join with Outer Join-Style Syntax. „OUTER JOIN why it is restricted to max 2 tables” is the main question. The question says that OUTER JOINS are restricet to 2 tables but realy are they ? Why it is written in thouse sources that there is a restriction? Basing on the information as I mentioned earlier from the two books we can make two questions/hypothesis. H0 - OUTER JOINS are restricted to two tables H1 - OUTER JOINS are not restricted two tables Lets consider folowing example: The macro is as simple as it can be. It is creating a join with a type of your choice and the number of tables. If someone do not understand the macro I will try to explain it in priv msg if someone wants or here. options mprint mcompilenote=all;
%macro innerouter(join=inner, numberoftables=255);
proc sql;
select a.z1
from a
%do i=1 %to &numberoftables;
&join join a
as a&i
on a.z1=a&i..z1
%end;
;
quit;
%mend;
Invocation of the macro and not producting error in the log:
%innerouter(join=left, numberoftables=255)
%innerouter(join=right, numberoftables=255)
%innerouter(join=full, numberoftables=255)
%innerouter(join=inner, numberoftables=255)
Invocation of the macro and producing error in the log:
%innerouter(join=left, numberoftables=256)
%innerouter(join=right, numberoftables=256)
%innerouter(join=full, numberoftables=256)
%innerouter(join=inner, numberoftables=256) LOG: ERROR: A maximum of 256 tables can be processed in a single PROC SQL statement. ERROR: Column z1 could not be found in the table/view identified with the correlation name A256. ERROR: Column z1 could not be found in the table/view identified with the correlation name A256. And the Note form the CERT BOOK ALSO: Following this logic it proves that every kind of horizontal join is processed in a single join and it is capable to proces a maxiumum of 256 table at a time. To be completly accurate to the information from the macro - numberoftables=255 is 255 tables + first table = 256 (the rest tables are self joined iterations). How to understand those informations from those books related to this point of view ? What author was thinking mentioning about maxiumum of two tables at a time? Is it a posible misleading text in those books? If I wrote something that is a crack in my logic or something that is not true just feel free and write want is wrong and what You think. %put Greetings _ALL_;
... View more