LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (October 1997, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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.


Back to: Top of message | Previous page | Main SAS-L page