I should clarify my prior post by pointing out that the unqualified xMin,
xMax, yMin, and yMax columns referenced in the query are all coming from the
"Kevin Myers" <KevinMyers@austin.rr.com> wrote in message news:...
> I'm running the following code under SAS 8.2 on Windows 2000 Pro:
> 1084 /* determine original grid blocks corresponding to each new grid
> 1085 proc sql;
> 1086 create table newGrid (drop=dc) as
> 1087 select newGrid.*, xc, yc, %dist(x,y,xc,yc) as dc from newGrid,
> MPRINT(DIST): sqrt(((xc)-(x))**2+((yc)-(y))**2)
> 1088 where newGrid.x between xMin and xMax and newGrid.y between yMin and
> 1089 group by x, y having calculated dc=min(calculated dc)
> 1090 order by xc, yc;
> NOTE: The execution of this query involves performing one or more
> product joins that can not be optimized.
> NOTE: The query requires remerging summary statistics back with the
> I am almost 100% certain that the SQL optimizer in SAS *does* have the
> ability to optimize where clauses that contain between conditions. Yet I
> have both sorted and indexed the newGrid table referenced in the above
> (ordered by x, y and composite index xy on x, y), and am still receiving
> message regarding a cartesian product join that can't be optimized. I let
> this query run to completion once, and it required more than 20 minutes
> the data that I am using, which is unacceptable.
> 1. Can anyone see something else that I am overlooking in the above query
> that is preventing it from being optimized?
> 2. Can anyone confirm whether optimization for between conditions is
> in SAS 8.2, or was that not implemented until SAS v9.x?