NetNews Usenet Archive 1992 #16/NN_1992_16.iso/spool/bit/listserv/sasl/3443

Text File | 1992-07-27 | 5.4 KB | 110 lines

Comments: Gated by [e-mail]
Path: sparky!uunet!paladin.american.edu!auvm!COMPUSERVE.COM!76350.1604
Message-ID: <[e-mail]>
Newsgroups: bit.listserv.sas-l
Date:         Mon, 27 Jul 1992 18:06:43 EDT
Reply-To:     Andy Norton <[e-mail]>
Sender:       "SAS(r) Discussion" <[e-mail]>
From:         Andy Norton <[e-mail]>
Subject:      SQL ? and SAS...
Comments: To: SAS-L <[e-mail]>
Lines: 97

Date:     7/27/92
Name:     Andy Norton [phone]     [e-mail]
Rel/Pltf: 6.06 and above
Re:       SQL basics (missionary tract)
          Response to Daryl Sawders
Address:  Trilogy Consulting, 5228 Lovers Lane, Kalamazoo MI 49002

Daryl Sawders requested basic information about PROC SQL.

I am going to join in harmony with the other SQL supporters on this
net.  SQL is fundamental and revolutionary.

1)  SQL works with _any_ SAS data sets, including traditional SAS data
files, SAS/ACCESS views, SQL views, and DATA-step views.

2)  Through some miracle, SQL is part of the base product in all
releases 6.06 and above.

3)  PROC SQL is an implementation of the SQL language, originally
developed by IBM.  It is supported by over 150 Database Management
Systems (DBMS's).  There is an ANSI standard, and proposals for future
standards.  The SAS implementation of SQL is more powerful than many
implementations (e.g. ORACLE).  SAS entered the SQL arena relatively
late, and benefited from watching everyone else.  Also, the SAS
implementation includes much of the proposed SQL2 standard.  There has
been a tremendous amount of research and experience with SQL, which
keeps SAS Institute honest (SQL facilitates direct comparison with
competitors).

4)  You can invoke the SQL processor from PROC SQL (or submit from
SCL).  But you can also create SQL views.  These are virtual datasets,
e.g. programs that are run on demand when you need their results.  This
is a key strength of SQL:  you can build views, and never have to
re-create intermediate files again.  Views are used in the same way as
datasets.  That is, you can be running SQL without realizing it.

5)  SQL is a declarative language, rather than algorithmic.  That is,
you tell the computer what to do by describing your goal rather than
step-by-step instructions.  This
    a) makes it easier for you and others to read and modify, because
you don't have to mentally translate between goals and actions,
    b) can lead to more efficient processing, because SQL attempts to
take any program and "optimize" it into ideal form  (On occasion, the
optimizer will choose an inefficient solution, and it can be difficult
to force a different solution).
    c)  any method can be used to achieve the goal.  These need not be
methods used by traditional SAS.   For example, the MERGE statement
requires sorting both data sets.  SQL, in contrast, may store the
smaller data set in a hash table and use no sorting at all.  This is
transparent to the user.
    d)  "client-server" processing is facilitated.  This fashionable
buzzword refers to processing different parts of a query on different
machines or as different tasks.  For example, if you specify a WHERE
clause in a SAS SQL program that references a SAS/ACCESS to ORACLE
view, the WHERE clause may be evaluated on the ORACLE side, reducing
network traffic.
    e)  Once you become fluent in SQL, you spend your time thinking
about data structures and about the content of the problem, rather than
physical implementation.  In my opinion, the usual result is a better
designed program, even if you (for some reason) want to re-write it in
traditional SAS code later.

6)  SQL is especially well suited to joins ("merges" in SAS
terminology), summarization, and subsetting.  It is ok for variable
construction.  It is poor for lag operations (but it can be done).

7)  The implementation of SAS SQL is getting better.  Release 6.07 is
much more efficient than 6.06, and provides additional features.
Release 6.08 will be even better.  Remember, this is a new product for
SAS Institute -- comparisons to mature products such as the DATA step
are unfair in that SQL will almost certainly improve more.

8)  SQL is not perfect.  See the series of books "Relational Database
Writings" by C.F. Date.  But it is a big step in the right direction
(compared to DATA steps).  Learning SQL will help you understand
whatever comes next.

9)  SQL works better with "normalized", vertical data structures.
"Normalized" means (very briefly) that variables are grouped into
different tables according to their level of analysis.  SQL prefers
vertical data structures because it does not support arrays.  Put each
"array element" into a separate observation.
     You can, however, use data with less than ideal structures.  It
just takes more work on the programmer's part.

==>  I have been a professional SAS programmer for 12 years, pretty
much full-time.  I am well-experienced in traditional SAS
coding techniques, but after using version 6 for two years, I find
myself using PROC SQL over 80% of the time.  I know lots of people are
reluctant to invest resources into the latest craze, but THIS IS
BIG.  SAS Institute has been slow to promote SQL ("not invented
here"?), but the word is getting out.

P.S.  I have to give credit where it is due.  Paul Kent is the SQL
developer at SAS Institute and clearly one of their top stars.
Thanks, Paul, for getting SAS Institute into the SQL business and
making it a success.