@MRDM
You've got subqueries also in the SQL Join transformation. Having said that I'm not a great fan of how the DIS windows behave for sub-query definitions. I'd like to get something like a pop-up window or a split screen for sub-queries so I can keep a graphical overview over the whole SQL while working on a sub-query.
I consider SQL's with sub-queries also much harder to debug so I normally try to keep my SQLs as simple as possible.
Given that SQL views are in a way nothing else than encapsulated SQL code what I'm normally doing if possible, is to have multiple nodes and to define the "sub-queries" upfront in their own SQL nodes creating a view - and then use these views in the downstream "master" query. This allows me to unit test and debug these "sub-queries" separately.
This is of course not always possible (ie. for an EXIST clause) and if interfacing with a database you want also to make sure that the SAS/Access engine doesn't get confused and is still able to send as much of the full SQL as possible to the database (use options sastrace=',,,d' sastraceloc=saslog nostsuffix; in such a case to verify in the log what part of the SQL gets actually sent to the database for in-database processing).
If going for this "upfront view" approach also use the FEEDBACK option for your main query.
proc sql;
create view v_class as
select *
from sashelp.class
;
quit;
proc sql feedback;
create table test as
select a.*
from
sashelp.class as a
inner join
v_class as b
on a.name=b.name
;
quit;
Using the FEEDBACK option you then see in the log what SAS actually makes out of your code.
NOTE: Statement transforms to:
select A.Name, A.Sex, A.Age, A.Height, A.Weight
from SASHELP.CLASS A inner join
( select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weight
from SASHELP.CLASS
) on A.Name = CLASS.Name;
And as DIS flow above would look like:
And you can set the FEEDBACK option here (as part of SQL Join node "Join with Class"):
... View more
