Date:         Mon, 6 Oct 1997 19:52:17 -0400
Reply-To:     Anthony Ayiomamitis <ayiomamitis@IBM.NET>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Anthony Ayiomamitis <ayiomamitis@IBM.NET>
Subject:      Re: SASTIP: optimising SQL
Content-Type: text/plain; charset=us-ascii

Don Stanley wrote:
>
> "Less obvious" SQL code
>
> create table newtable as
>    select a.*,b.p_date from
>      million9(where=(outcomec=0)) a,
>      million4(where=(p_date ne .)) b
>   where a.case_id = b.case_id ;
>
> Now the records are pre-filtered by the dataset WHERE clauses in the
> SQL, and you can acheive massive resource reduction (especially if you
> have a situation where only a few records would be returned by either of
> the WHERE clauses.
>
> "Even Better" SQL code
>
> create table newtable as
>    select a.*,b.p_date from
>      million9(where=(outcomec=0)) a,
>      million4(where=(p_date ne .) keep=p_date case_id) b
>   where a.case_id = b.case_id ;
>
 
Don,
 
    I purposely left this "solution" out of my rebuttal to Adam hoping
that someone, preferably Adam, would raise the issue of using data step
commands within SAS's version of SQL. Unfortunately, it is you and not
Adam that has now supplied me with what I have been waiting for the past
few days.
 
    Adam made the comment that a C programmer, for example, would pick
up PROC SQL code much easier than data step code given the universal
nature of SQL.
 
    Well, now I ask, how will the same C programmer pick up modified SQL
which has been expanded by SAS to implement data step features? In fact,
if these "enhancements" to standard SQL are comprehensible to the
infamous C programmers that Adam works with, then a good portion of his
previous message and attempted points can be thrown out the window.
 
    Furthermore, these data step "adjuncts" remove some of the power of
SQL. One of the most obvious is the ability to join using different
column names ("where a.id = b.id_num") which is mitigated by the ability
to do the rename within the SAS-SQL code using data step commands.
 
    Using an example I mentioned earlier, namely the dropping of a
column from a table, I went to great extents to describe how a DBMS
individual who was non-literate in SAS would go about such a feat. Now,
I ask Adam who took pride in stating that PROC SQL can be understood
much easier and quicker by C programmers and DBMS individuals, how would
their knowledge of standard SQL have benefitted them in deducing your
second example above? The answer is absolutely none! However, if the
same individuals knew data step code, they would have been able to
derive a much concise and efficient solution to the problem at hand
(i.e. dropping a column) using data step features, thereby showing that
for this example, at least, native SQL is inferior to data step code.
 
Anthony.