sqlug2.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLUG2                                              */
 /*   TITLE: EXAMPLES FROM CHAPTER TWO OF SQL USER'S GUIDE       */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN SQLV61 MACRO FEEDBACK PRINT SUM GROUP in */
 /*          ORDER BY WHERE LIKE BETWEEN NULL HAVING VALIDATE    */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS, PMK                    UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
  title1 '*** sqlug2: SQL User Guide chapter two examples ***';


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

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;

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;


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


 /*
 * This example selects and displays the list price and product
 * name from the product table using proc print and proc sql.
 */

proc print data=product noobs;
run;

proc sql;
   title2 'List Prices and Product Names';
   select prodlist, prodname
     from product;

 /*
 * In this example an asterick (*) is used in place of column names
 * to select all the columns from the product table.  The order of
 * the columns displayed matches the order of the columns in the
 * table.  The FEEDBACK option writes the expanded form of the
 * SELECT on the SAS log.
 */

   proc sql feedback;
   select *
      from product;


 /*
 * This example demonstrates the SUM function and the use of a
 * column alias.  This SELECT statement selects salesreps and
 * displays the salesrep city and the total number of service
 * years by city, sorted by the total number of service years.
 */

   proc sql;
   title2 'Salesrep information, sorted by years of service';
   select empcity, sum(empyears) as totyears
     from employee
    where emptitle = 'salesrep'
    group by empcity
    order by totyears;


 /*
 * This example demonstrates that arithmetic expressions in the
 * SELECT clause can perform computations on numeric columns.  The
 * query below displays the percentage profit margin for each
 * product sold at fulllist price.
 */

   title2 'Percentage of profit margin for each product';
   proc sql;
   select prodname, prodlist, prodcost,
          (prodlist - prodcost) / prodlist
     from product;

 /*
 * The following query selects all the columns and rows
 * from the Customer table and displays the resulting table.
 */

   title2 'Customer Table';
   proc sql;
   select * from customer;


 /*
 * This example demonstrates GROUP BY.  In this query, the results
 * of the summary function are grouped by the city name, that is,
 * the number of EMPYEAR for each city are added using the SUM
 * function.
 */

   title2 'Total years of service, by city';
   select empcity, sum(empyears) as totyears
     from employee
    where emptitle = 'salesrep'
    group by empcity;


 /*
 * This example demonstrates ORDER BY.  The query below displays
 * the Product table sorted by ascending list price.
 */

   title2 'Product table sorted by list price';
   proc sql;
   select prodname, prodlist, prodcost
     from product
    order by prodlist;


 /*
 * This example demonstrates ORDER BY.  The query computes the
 * percentage profit margin for each product sold at full list
 * price.  The results are sorted in descending order by the
 * fourth column in the SELECT statement and in ascending order
 * by prodcost when the values of the fourth column have the same
 * value.
 */

   title2 'Profit Margins';
   proc sql;
   select prodname, prodlist, prodcost,
          (prodlist - prodcost) / prodlist
     from product
    order by 4 desc, prodcost asc;


 /*
 * This is an example of a WHERE expression using a comparison
 * operator.  This query on the Employee table displays only
 * those employees who have ten or more years of service.
 */

   title2 'Employess with 10 or more years of service';
   proc sql;
   select empname, emptitle
     from employee
    where empyears >= 10
    order by empname;


 /*
 * This is an example of a COMPOUND predicate.  The query
 * below displays employees who neither live in Ocean City
 * nor function as salesreps but who have more than ten
 * service years.
 */

   title2 'EMPLOYEES WHO DO NOT LIVE IN OCEAN CITY NOR FUNCTION';
   title3 'AS A SALESREP BUT HAVE MORE THAN TEN YEARS OF SERVICE';
   proc sql;
   select empname, emptitle, empcity, empyears
     from employee
    where (not (empcity = 'Ocean City' or emptitle = 'salesrep'))
      and (empyears > 10);


 /*
 * These examples demonstrate the like operator used for pattern
 * matching.  This query displays each employee whose name begins
 * with the uppercase letter "S."
 */

   title2 'Employees whose name begins with "S"';
   proc sql;
   select empname
     from employee
    where empname like 'S%';


 /*
 * The query below selects each employee whose name ends with a
 * lowercase "k" and consists of exactly four letters.
 */

   title2 'Employees who have a four letter name ending in "k"';
   select empname
     from employee
    where empname like '___k';


 /*
 * This example demonstrates a WHERE expression which tests for a
 * membership in a set of values.  This query displays employees
 * who have worked for one, five, or ten years.
 */

   title2 'Employees who have worked for one, five, or ten years';
   proc sql;
   select empname, empyears
     from employee
    where empyears in (1,5,10);


 /*
 * This example demonstrates a WHERE expression testing for a
 * range of values.
 * In the query the BETWEEN range selects and displays all the rows
 * whose employee numbers fall between the employee numbers 301 and
 * 401, including these numbers.
 */

   title2 'Employee numbers between 301 AND 401';
   proc sql;