sqlug3.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLUG3                                              */
 /*   TITLE: EXAMPLES FROM CHAPTER THREE OF SQL USER'S GUIDE     */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN SQLV61 MACRO CREATE TABLE SELECT ORDER   */
 /*          BY LIKE DROP VIEW SUM GROUP CONTENTS ALTER          */
 /*          UPDATE SET CASE WHEN AVG HAVING RESET DELETE        */
 /*          UNDO_POLICY COALESCE FULL JOIN DISTINCT             */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS, PMK                    UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
  title1 '*** sqlug3: SQL User Guide chapter three 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 customer;
   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 employee;
   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 THREE OF SQL USER'S GUIDE BEGIN HERE-*/
 /*--------------------------------------------------------------*/


 /*
 * This example demonstrates how to create a temporary table from
 * a permanent table.  This table contains employees who have
 * worked for the sample wholesale company for more than six years
 * and who are salesreps.
 */

proc sql;
   title2 'Senior staff table';
   create table work.senior as
   select empname, empyears, emptitle
     from  employee
    where empyears >= 6 and emptitle = 'salesrep'
    order by empyears desc;

   select * from work.senior;

 /*
 * In this example the DROP data set option is used when creating
 * a permanent table from an existing permanent table.  The SUBINV
 * table is a duplicate of the INVOICE table, with the exception
 * of omitting the invnum and empnum columns.
 */

   create table subinv(drop=invnum empnum)
      like invoice;

   select * from subinv;

 /*
 * This next example uses a column definition list (in parentheses)
 * to define the columns and their attributes in the RIVALS table.
 * The column attributes defined are data type, length, informat,
 * and format.
 *
 * In the SELECT statement, the column sale_mon is defined with the
 * format DOLLAR.  No result table is displayed because the Rivals
 * table contains no data.
 *
 * The rivals table is then dropped.
 */

   create table rivals
      (competit  char(12),         /* competitor's name */
       compcity  char(12),         /* competitor's city */
       custname  char(12),         /* customer's name   */
       prodname  char(12),         /* product name      */
       sale_mon  num informat=dollar10.2  format=dollar.
                                  /* competitor's monthly sales */
      );

   select competit, compcity, custname, prodname,
          sale_mon format=dollar.
     from rivals;

   drop table rivals;


 /*
 * This example demonstrates how to create a temporary view
 * BIGSALE and retrieve data from it.  The view's SELECT statement
 * lists invoices in which the total sale amount exceeds $1500.
 * The Employee table is joined with the Invoice table so that the
 * employee name, instead ofemployee number, can appear in the
 * query result.
 */

   create view bigsale as
   select invnum, custname, custnum, empname,
          prodname, invqty, invprice
     from invoice as i, employee as e
    where i.empnum = e.empnum  and (invqty * invprice) > 1500;

   title2 'Bigsale view sorted by invnum';
   select * from bigsale order by invnum;



 /*
 * The statements below create and display a permanent view
 * named HIGHQTY. The view is stored in a permanent SAS dataset
 * by using a two-level name for it.
 *
 * Stored views can be described to the SAS log.
 */

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

   title2 'Highqty view';
   select * from highqty;

   describe view highqty;


 /*
 * This example demonstrates the FEEDBACK option.  This option
 * allows an expanded version of the view to be displayed, listing
 * all the columns in the SELECT clause.
 */

   proc sql feedback;
   title2 'Highqty view';
   select * from highqty;

   reset nofeedback;


 /* This example demonstrates how to delete a view using the DROP
 * VIEW statement.  The example creates and then deletes the Names
 * view definition.
 */

   create view names as
      select prodname from product;

   drop view names;


 /*
 * This example demonstrates an in-line view, join, and summary
 * function. The example lists the salesreps who have sold 20 or
 * more rafts and the number of rafts that each sold.
 */

   title2 'Salesreps who have sold 20 or more rafts';
   select empname, numraft
     from ( select empname, sum(invqty) as numraft
              from invoice i, employee e
             where prodname = 'raft'  and i.empnum = e.empnum
             group by empname
          )
    where numraft >= 20
    order by 1;


 /*
 * The example below uses the PRINT procedure to display the data
 * defined by the Highqty view. All SAS procedures can process SQL
 * views as if  they were SAS datasets.  Notice the differences
 * between the CONTENTS of the Highqty table and that of the
 * Highqty's underlying table, Invoice.
 */

title2 'Highqty view with SAS Procedures';
proc print data=highqty noobs;
   run;

proc contents data=highqty;
   run;

proc contents data=invoice;
   run;


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


 /*
 * The following example creates a permanent table, NEWPRICE.
 * It then adds a new column to that table.
 */


proc sql;
   title2 'Newprice table';
   create table newprice as
   select prodname as product, prodcost as cost,
          prodlist label='list price'
     from product;

   alter table newprice
      add list1989 num format=dollar.;

   select * from newprice;

 /*
 * An alternative way of creating the newprice table is to compute
 * the new price column as you create the table.
 */

   create table newprice as
   select prodname as product, prodcost as cost,
          prodlist label='List Price',
          (prodlist * 1.2) as list1989 format=dollar.
     from product;

   select * from newprice;


 /*
 * This example demonstrates using the ALTER statement to change
 * the format of the columns.
 */

   alter table newprice
      modify cost     num format=dollar10.2,
             prodlist num format=dollar10.2,
             list1989 num format=dollar10.2;

   title2 'Newprice table with new formats';
   select * from newprice;

 /*
 * This example demonstrates using the ALTER statement to drop a
 * column.
 */

   alter table newprice
      drop cost;

   title2 'Newprice table without cost';
   select * from newprice;


 /*
 * This example demonstrates an UPDATE statement with and without
 * a WHERE  expression.  In the first UPDATE statement below all
 * values are updated int the LIST1989 column because the WHERE
 * expression was omitted. In the second UPDATE statement, the
 * WHERE expression eliminates rows that have values greater than
 * 240 in the PRODLIST column.  The third UPDATE statement supplies
 * values for products whose prices were not changed by the first
 * statement.
 *
 * Note that this approach requires two passes over the dataset,
 * one for each update statement.
 */

   create table newprice as
   select prodname as product, prodcost as cost,
          prodlist label='list price'
     from product;

   alter table newprice
      add list1989 num format=dollar.;

   update newprice
      set list1989 = prodlist * 1.2;

   title2 'Newprice table after udpate';
   select * from newprice;

   update newprice
      set list1989 = prodlist * 1.2
    where prodlist <= 240;

   update newprice
      set list1989 = prodlist
    where prodlist > 240;

   select * from newprice;

 /*
 * This example uses a CASE expression, and effects the update in
 * a single pass over the newprice dataset. The case expression
 * returns the multiplier to be used to compute list1989 from
 * prodlist.
 */

   update newprice
      set list1989 = prodlist *
              case when prodlist <= 240 then 1.2
                   else 1
                   end
          ;

   title2 'Newprice table using a case expression';
   select * from newprice;


 /*
 * This example demonstrates the INSERT statement to insert rows.
 * You can insert constant values, or the results for some query
 * expression.
 *
 * It also demonstrates the delete statement to remove rows from
 * a table.
 */

   create table newprice as
   select prodname as product, prodcost as cost,
          prodlist label='list price',
          (prodlist * 1.2) as list1989 format=dollar.
     from product;


   insert into newprice
      set product  = 'umbrella',
          cost     = 12,
          prodlist = 15,
          list1989 = 18;

   title2 'Newprice table after insert';
   select * from newprice;

   insert into newprice
      values('umbrella',12,15,18);

   /* The default value of the UNDO_POLICY option will
      cause PROC SQL to obtain exclusive access to the
      dataset being inserted into (newprice).
      The second reference to the same table will fail.

      Choosing UNDO_POLICY=OPTIONAL allows this query to proceed.

      For details on the UNDO_POLICY option, please refer to the
      607 changes and enhancements documentation.                 */

   reset undo_policy=optional;

   insert into newprice
   select trim(product) || " GT", cost * 1.2 as cost,
          prodlist * 1.3 as prodlist,
          list1989 * 1.3 as list1989
      from newprice
      where product in ('raft','kayak');

   select * from newprice;


   /* reset to default undo_policy=required */
   reset undo_policy=required;

   delete
      from newprice
      where product = 'umbrella';


 /*
 * The example demonstrates the RESET option which is used to add,
 * drop, or change the options on the PROC SQL statement.
 */

   proc sql noprint;
   select * from product;

   reset print number;
   select * from product
      where prodcost < 20;


 /*=============================================================*/
 /*==              SQL and summary statistics.                ==*/
 /*=============================================================*/


 /*
 * In this example, a summary function is used alone in the
 * SELECT clause so it is evaluated without remerging the data.
 */

proc sql;
    title2 'Average years of employment';
    select avg(empyears) as avg
      from employee;


 /*
 * In this example, the data is not remerged because the average-
 * number-of-years values is associated with each row of the
 * GROUP BY item, EMPTITLE.
 */

   select emptitle, avg(empyears) as avg
     from employee
     group by emptitle;


 /*
 * This example demonstrates how it is possible to get unexpected
 * results from a query that remerges data.  This query calculates
 * the average based on all the EMPYEARS (that is, EMPYEARS is
 * treated as one group).
 */

   title2 'Average years of employment';
   select emptitle, avg(empyears) as avg
     from employee;