sqlug4.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLUG4                                              */
 /*   TITLE: EXAMPLES FROM CHAPTER FOUR OF SQL USER'S GUIDE      */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN SQLV61 CREATE TABLE VIEW INSERT WHERE BY */
 /*          CONTENTS UNIQUE INDEX DROP DELETE ORDER UPDATE      */
 /*          VALIDATE &SQLOBS &SQLOOPS &SQLRC WHERE BETWEEN      */
 /*          CASE WHEN SUM PRINT                                 */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS, PMK                    UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC: the following tables names were shortened to        */
 /*          seven characters, so that these samples will        */
 /*          run on VM/CMS                                       */
 /*                                                              */
 /*          EMPLOYEE  --  EMPLOYE                               */
 /*          CUSTOMER  --  CUSTOMR                               */
 /*          SALEREPS  --  SALEREP                               */
 /*          SALESNUM  --  SALENUM                               */
 /*                                                              */
 /****************************************************************/

  title1 '*** sqlug4: SQL User Guide chapter four examples ***';


 /*-----------------------------------------------------------------*/
 /*-- The following data steps create the permanent datasets used --*/
 /*-- in the SQL User's Guide examples.                           --*/
 /*-----------------------------------------------------------------*/

data product;
   input prodname $ 1-10 prodcost prodlist;
   format prodcost prodlist dollar.;
   cards;
flippers      16    20
jet ski     2150  2675
kayak        190   240
raft           5     7
snorkel       12    15
surfboard    615   750
windsurfer  1090  1325
run;

data customr;
   input custname $ 1-10 custnum custcity $ 22-36;
   cards;
Beach Land       16  Ocean City
Coast Shop        3  Myrtle Beach
Coast Shop        5  Myrtle Beach
Coast Shop       12  Virginia Beach
Coast Shop       14  Charleston
Del Mar           3  Folly Beach
Del Mar           8  Charleston
Del Mar          11  Charleston
New Waves         3  Ocean City
New Waves         6  Virginia Beach
Sea Sports        8  Charleston
Sea Sports       20  Virginia Beach
Surf Mart       101  Charleston
Surf Mart       118  Surfside
Surf Mart       127  Ocean Isle
Surf Mart       133  Charleston
run;

data employe;
   input empnum empname $ empyears empcity $ 20-34
   emptitle $ 36-45 empboss;
   cards;
101   Herb     28  Ocean City      president     .
201   Betty     8  Ocean City      manager     101
213   Joe       2  Virginia Beach  salesrep    201
214   Jeff      1  Virginia Beach  salesrep    201
215   Wanda    10  Ocean City      salesrep    201
216   Fred      6  Ocean City      salesrep    201
301   Sally     9  Wilmington      manager     101
314   Marvin    5  Wilmington      salesrep    301
318   Nick      1  Myrtle Beach    salesrep    301
401   Chuck    12  Charleston      manager     101
417   Sam       7  Charleston      salesrep    401
run;

data invoice;
   input invnum custname $ 6-15 custnum empnum prodname $ 28-37
      invqty invprice;
   format invprice dollar.;
   cards;
280  Beach Land   16  215  snorkel      20    14
290  Beach Land   16  216  flippers     15    19
300  Beach Land   16  216  raft         20     7
310  Coast Shop    3  318  windsurfer    2  1305
320  Coast Shop    3  318  raft         30     6
330  Coast Shop    5  318  snorkel       5    15
340  Coast Shop    5  318  flippers     15    19
350  Coast Shop    5  318  raft         40     6
360  Coast Shop    5  318  snorkel      10    15
370  Coast Shop   12  213  raft         10     7
380  Coast Shop   14  417  windsurfer    1  1325
390  Del Mar       3  417  flippers     30    18
400  Del Mar       3  417  kayak         3   230
410  Del Mar       8  417  raft         40     6
420  Del Mar      11  417  raft         15     7
430  Del Mar      11  417  snorkel      10    15
440  Del Mar      11  417  flippers     20    19
450  New Waves     3  215  flippers      5    20
460  New Waves     3  215  flippers     10    20
470  New Waves     6  213  snorkel      15    15
480  New Waves     6  213  surfboard     4   735
490  New Waves     6  213  snorkel      10    15
500  Surf Mart   101  417  snorkel      20    14
510  Surf Mart   101  417  surfboard     2   740
520  Surf Mart   101  417  snorkel      12    15
530  Surf Mart   118  318  flippers     15    19
540  Surf Mart   118  318  raft         30     6
550  Surf Mart   118  318  snorkel      10    15
560  Surf Mart   127  314  flippers     25    19
570  Surf Mart   127  314  surfboard     3   740
run;


 /*-----------------------------------------------------------------*/
 /*--  EXAMPLES FROM CHAPTER FOUR OF SQL USER'S GUIDE BEGIN HERE  --*/
 /*-----------------------------------------------------------------*/


 /*=================================================================*/
 /*==                 SQL for Data Management.                    ==*/
 /*=================================================================*/

 /*
 * This is an example of how to CREATE a view. The view's SELECT
 * statement lists all the invoices that show sales of 25 or more
 * items. The feedback option shows the expansion of the * in the
 * select clause.
 */

proc sql;
   create view highqty as
      select *
      from   invoice
      where  invqty >= 25;


   reset feedback;
   title2 'Highqty table';
   select * from highqty;


 /*
 * This example demonstrates the CREATE and INSERT statement.  The data
 * set options TYPE= and LABEL= are used. They can be intermixed with
 * SQL variable(column) specifications.
 */

proc sql;
   create table salaries (
                type=data label='Salaries Table',
                lastname char(15),
                fname char(12),
                ssnumber num format=SSN11.,
                salary num label='Monthly Salary' format=dollar.,
                annsal num label='Annual Salary' format=dollar10.
                             );

   insert into salaries
      values('Conway','Kathryn',224223312,4325,51900)
      values('Schneyer','Samantha',321538796,1275,15300)
      values('Stein','Joel',323093467,3211,38532)
      values('Rodriguez','Jose',123994563,3356,40272)
      values('Johnston','Lois',276116745,2444,29328)
      values('Wong','William',321684532,1998,23976)
      ;

   title2 'Salaries table';
   select * from salaries;

 /*
 * The label given to the SAS dataset is displayed by the CONTENTS
 * procedure.
 */

proc contents data=salaries;
   run;


 /*
 * This example creates another table using a different form of the
 * CREATE table statement.  Any valid SAS dataset option can be
 * specified in the parenthesised list after the dataset name that
 * is being created.
 */

proc sql;
   create table midrange(label='Midrange Salaries') as
   select *
     from salaries
    where salary > 2000 and salary < 3999;

   title2 'Midrange table';
   select * from midrange;

   create view lowsals as
   select *
     from salaries(drop=annsal)
    where salary < 2000;

   select * from lowsals;


 /*
 * This example shows how you can use SAS code and the SQL procedure
 * together.  The DATA step creates the data set Alterex
 * and the SQL procedure alters its columns.
 */

data alterex;
   input cc $ nn;
   cards;
   Hello 1
   Goodbye 2
   ;

proc sql;
   alter table alterex
      modify cc  char(5),    /* this column will be shortened */
             nn2 num;        /* this column will be created   */

   title2 'Alterex table after alterations';
   select * from alterex;

   alter table alterex
     drop nn2;

   title2 'Alterex table after column "nn2" is dropped';
   select * from alterex;

 /* These examples demonstrate how to create a table and view
 * using PROC SQL.
 */

   create table service as
   select empname, empyears
     from employe
     where empboss is not null;

   proc print noobs;
     title2 'Employee Service Records';
     run;

   proc sql;
   select * from service;

   create view view1 as
      select * from invoice where invqty > 10;


 /*=================================================================*/
 /*==                     SQL and Views.                          ==*/
 /*=================================================================*/


 /* This example creates a Birthday data set and then uses the SQL
 * procedure to create and display a view derived from the data set.
 * The view derives a coulmn for AGE from the persons bithday.
 *
 * This example demonstrates the option of omitting the libref in the
 * FROM clause of a CREATE VIEW statement.  The libref is optional
 * if the table and view are in the same data library.  Birthday and
 * bday are stored permanently in the same data library SQL.
 */

data birthday;
   input name $ bday date7.;
   format bday date7.;
   cards;
   Jenny 04feb63
   Sally 10feb66
   ;

proc sql;
   create view bday as
   select name, bday, (today()-bday)/365.25 as age format=6.2
     from birthday
    order by name;

   title2 'data set with ages computed';
   select * from bday;


 /*
 * This next example shows that a view can also be used in
 * SAS procedures other than PROC SQL.
 */

   proc print data=bday;
   title2 'Views can be accessed by other SAS procedures';
   run;


 /*=================================================================*/
 /*==                     SQL and indexes.                        ==*/
 /*=================================================================*/

 /*
 * The SQL procedure can create indexes on SAS datasets. An index
 * may have a UNIQUE property -- which wil ensure unique values
 * for that variable across all rows.
 *
 * Indexes may be based on more than one column.
 *
 * Indexes are removed with the DROP INDEX statement.
 */


proc sql;
   create unique index empnum on employe(empnum);
   create index custno on customr (custname, custnum);

   drop index empnum from employe;
   drop index custno from customr;


 /*=================================================================*/
 /*==                 Deleting Data with SQL                      ==*/
 /*=================================================================*/


 /*
 * In this example, a copy is made of the Customer table so that the
 * permanently stored table is not modified.  Here, all the customers
 * with stores in Virginia Beach are eliminated.
 */

   create table cities as
   select * from customr;

   delete
     from  cities
     where custcity = 'Virginia Beach';

   title2 'CITIES TABLE';
   select * from cities;

 /*
 * This is an example of the DESCRIBE statement.  This statement
 * displays the definition of a view on the SAS log.
 */

   create view manager as
   select empnum, empname, empcity
      from employe
      where emptitle = 'manager';

   title2 'Managers';
   select * from manager;

   describe view manager;


 /*=================================================================*/
 /*==               Deleting Objects with SQL                     ==*/
 /*=================================================================*/


 /*
 * This example demonstrates the DROP statement which deletes
 * the entire table, view or index requested.
 */

   create table salerep as
   select empnum
     from employe
    where emptitle = 'salesrep';

   create index empnum on salerep (empnum);

   drop index empnum from salerep;
   drop table salerep;


 /*=================================================================*/
 /*==              Inserting data into tables.                    ==*/
 /*=================================================================*/

 /*
 * This example demonstrates the INSERT statement using the
 * VALUES clause.
 */

   insert into employe
     values(420,'Susan',1,'Charleston','salesrep',401);

   title2 'EMPLOYEE TABLE';
   select * from employe;

   delete from employe where empname='Susan';


 /*
 * This is an example of the INSERT statement using the SET clause.
 * It will insert three rows, one for each set clause.
 */

   create table insertex ( x numeric, y numeric, z char );

   insert into insertex
      set x=1, y=3
      set z='hello', y=2
      set z='goodbye'
      ;

   title2 'INSERTEX TABLE';
   select * from insertex;

 /*
 * This example will be rejected because y was not listed with
 * Insertex(x,z).  Leaving off the optional column list after the
 * table name would be valid as shown in the next example.
 */

 /*   insert into insertex(x,z)
      set x=1, y=3
      ;
*/
   insert into insertex
      set x=1, y=3
      ;

 /*
 * This example demonstrates the DELETE statement and the INSERT
 * statement with the VALUES clause.  The second INSERT statement
 * adds two rows that contain missing values.  The last INSERT
 * statement is rejected because y was not listed with Insertex(x,z).
 */

   delete from insertex;

   insert into insertex
      values(1,2,'happy')
      values(3,4,'sad');

   title2 'INSERTEX TABLE';
   select * from insertex;

   insert into insertex(z,y)         /* x values will be missing */
      values('happier', 5)
      values('very sad', 6);

   title2 'INSERTEX TABLE';
   select * from insertex;


 /*
 * This example demonstrates the INSERT statement.  Notice that
 * the order of the values in the query expression matches the
 * order of the columns in the INSERT column list.
 */

proc sql;

   delete from insertex;

   insert into insertex(z,x,y)
   select empname, empnum, empyears
      from employe
      where empname like 'J%';

   title2 'INSERTEX TABLE';
   select * from insertex;


 /*=================================================================*/
 /*==              Selecting data from tables.                    ==*/
 /*=================================================================*/


 /*
 * This example SELECTs the employe table using an ORDER BY clause.
 * There are many other examples of the select statement in other
 * sections of this sample.
 */

   title2 'Employees in seniority order';
   select empname
     from employe
    order by empyears desc;


 /*=================================================================*/
 /*==                 Updating data in tables.                    ==*/
 /*=================================================================*/

 /*