Date:         Wed, 19 May 2010 02:45:24 -0400
Reply-To:     oloolo <dynamicpanel@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         oloolo <dynamicpanel@YAHOO.COM>
Subject:      Re: maximum nesting of subqueries

SAS9.1.3 TS1M3
WinXP 3Gb memory
failed at 22, didn't try the batch mode though
but the actual code shows 20 selection clauses in 22-iteration mode
===================
141
142  proc sql;
143  %subsubquery(%query,22)
MPRINT(SUBQUERY):   select 1 from (select 1 from (select 1 from (select 1
from (select 1 from (select
1 from (select 1 from (select 1 from (select 1 from (select 1 from (select
1 from (select 1 from
(select 1 from (select 1 from (select 1 from (select 1 from (select 1 from
(select 1 from (select 1
from (select 1 from R)))))))))))))))))))
144  ;
145  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


146
147
148  proc sql;
149  %subsubquery(%query,23)
MPRINT(SUBQUERY):   select 1 from (select 1 from (select 1 from (select 1
from (select 1 from (select
1 from (select 1 from (select 1 from (select 1 from (select 1 from (select
1 from (select 1 from
(select 1 from (select 1 from (select 1 from (select 1 from (select 1 from
(select 1 from (select 1
from (select 1 from (select 1 from R))))))))))))))))))))
150  ;
ERROR: Query is too complex to be processed. It references more than 16
tables.
151  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds




On Tue, 18 May 2010 23:30:33 -0400, Sigurd Hermansen <HERMANS1@WESTAT.COM>
wrote:

>I recall seeing maximum nesting of subqueries increase from 16 to 32 at
one time, but don't find anything in the V9.2 documentation. If anyone
knows the official maximum, I'll appreciate an update.
>
>Using the Skeptic Whitlock method, I've determined that the simplest
nesting of queries in SAS SQL differs between DM and batch executions of
SAS programs. This test under DM fails at 114 but executes normally at the
strange number 113 (or is it 112?). Batch submissions fail at far lower
numbers. I find these results puzzling and strange.
>
>Anyone willing to test for a maximum number under different versions and
OS's? Here's the test program:
>
>/*Test for maximum level of embedding of SAS SQL queries.*/
>data R;
>a=1;
>run;
>options mprint;
>%macro query;
>select 1 from R
>%mend;
>%macro subquery(__q);
>select 1 from (&__q)
>%mend;
>%macro subsubquery(__sq,__n);
>%do __i = 3 %to %eval(&__n - 1);
>   %subquery(
>%end;
>   &__sq
>%do __i = 3 %to %eval(&__n - 1);
>    )
>%end;
>%mend;
>proc sql;
>%subsubquery(%query,113)
>;
>quit;