sqlug1.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLUG1                                              */
 /*   TITLE: EXAMPLES FROM CHAPTER ONE OF SQL USER'S GUIDE       */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN SQLV61 SELECT SUM WHERE GROUP ORDER BY   */
 /*          SUMMARY SORT PRINT                                  */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS LBC                     UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
  title1 '*** sqlug1: User guide chapter 1 examples ***';

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;



 /*
 * This is an example of a SELECT statment that retrieves and
 * displays the name, city and number of service years for sales
 * representatives in the employee table.
 */

proc sql;
   title2 'CITY AND YEARS OF SERVICE';
   select empname, empcity, empyears
     from employee
    where emptitle = 'salesrep';


 /*
 * This example shows that the SQL procedure can achieve the
 * same results as base SAS coding but often with fewer and
 * shorter statements.  This SELECT statement performs summation,
 * grouping, sorting, and row selection.  It also executes the
 * query without using the RUN statement and automatically
 * displays the query's results without using the PRINT procedure.
 */

   title2 'TOTAL SERVICE YEARS';
   title3 'Computed with PROC SQL';
   select empcity, sum(empyears) as totyears
     from employee
    where emptitle = 'salesrep'
    group by empcity
    order by totyears;



 /*
 * This example demonstrates how a SAS program can produce the
 * same results as PROC SQL by using PROC SUMMARY, PROC SORT AND
 * PROC PRINT
 */

title2 'TOTAL SERVICE YEARS';
title3 'Computed with PROCS SUMMARY, SORT AND PRINT';
proc summary data=employee;
   where emptitle='salesrep';
   class empcity;
   var empyears;
   output out=sumyears sum=totyears;
   run;

proc sort data=sumyears;
   by totyears;
   run;

proc print data=sumyears noobs;
   var empcity totyears;
   where _type_ = 1;
   run;




 /*
 * In this example the OCITY view below consists of an
 * SQL SELECT statement that retrieves the employee name,
 * city, title and number of service years for those who live
 * in Ocean City.  When the OCITY view name is used as input
 * to the PRINT procedure, the view's SELECT statement is executed
 * to build a SAS data set that becomes the PRINT procedure's
 * input PROC PRINT then sums the number of service years for
 * employees.
 */



proc sql;
   title2 'EMPLOYEES WHO RESIDE IN OCEAN CITY';
   create view ocity as
   select empname, empcity, emptitle, empyears
     from employee
    where empcity = 'Ocean City';

proc print data = ocity;
   sum empyears;
   run;