sqlsetop.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLSETOP                                            */
 /*   TITLE: SQL SET OPERATORS -- UNION, INTERSECT AND EXCEPT    */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN CASE WHEN UNION INTERSECT SELECT         */
 /*          ORDER BY                                            */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS, PMK                    UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
  title1 '*** sqlsetop: basic SQL set operations ***';

  /*---------------------------------------------------------------*/
  /*-- The first step is to create the Paper, Roundt and Section --*/
  /*-- tables which will be used in the following queries.       --*/
  /*---------------------------------------------------------------*/

data paper;
 input author$1-8 section$9-16 title$17-43 @45 time time5.
       duration;
 format time time5.;
 label title='Paper Title';
 cards;
Tom     Testing Automated Product Testing    9:00 35
Jerry   Testing Involving Users              9:50 30
Nick    Testing Plan to test, test to plan  10:30 20
Peter   Info SysArtificial Intelligence      9:30 45
Paul    Info SysQuery Languages             10:30 40
Lewis   Info SysQuery Optimisers            15:30 25
Jonas   Users   Starting a Local User Group 14:30 35
Jim     Users   Keeping power users happy   15:15 20
Janet   Users   Keeping everyone informed   15:45 30
Marti   GraphicsMulti-dimensional graphics  16:30 35
Marge   GraphicsMake your own point!        15:10 35
Mike    GraphicsMaking do without color     15:50 15
Jane    GraphicsPrimary colors, use em!     16:15 25
;

data section;
 input section$1-8 room$ convenor$;
 cards;
Graphics  Sable  Denise
Info Sys  Kudu   Peter
Testing   Sable  Jenny
Users     Kudu   Sally
;

data roundt;
 input leader$1-8 subject$9-30;
 label subject='Roundtable Subject';
 cards;
Mary    External DBMS's
Nick    Testing Networks
Jerry   User Specifications
Peter   Selling Solutions
Jim     Distasteful Jokes
Marge   Designing Fonts
;


proc sql;


  /*-----------------------------------------------------------*/
  /*-- What authors speak in the morning and also lead       --*/
  /*-- roundtables?                                          --*/
  /*-----------------------------------------------------------*/

  title2 'Authors who speak in the morning';
  title3 '    and lead roundtables';
  select author from paper where time < '12:00't
  intersect
  select leader from roundt;


  /*-----------------------------------------------------------*/
  /*-- What authors speak in the afternoon and do not lead   --*/
  /*-- roundtables?                                          --*/
  /*-----------------------------------------------------------*/

  title2 'Authors who speak in the afternoon';
  title3 '   and do not lead roundtables';
  select author from paper where time > '12:00't
  except
  select leader from roundt;


  /*-----------------------------------------------------------*/
  /*-- To help registration run more smoothly, people with   --*/
  /*-- duties at the conference will be divided, alphabeti-  --*/
  /*-- cally, into three groups.                             --*/
  /*--                                                       --*/
  /*-- the query proceeds in two phases.                     --*/
  /*--                                                       --*/
  /*--   1) forms a list of all names on the from clause.    --*/
  /*--   2) breaks that list into groups with the case       --*/
  /*--      expression on the object clause.                 --*/
  /*-----------------------------------------------------------*/

  title2 'Registration Groups';
  select case when name < 'G'     then 'A-G'
              when name < 'P'     then 'H-O'
              when name <= 'ZZZZ' then 'P-Z'
              else '???'
              end as group,
         name
    from
          ( select author as name from paper
            union
            select convenor from section
            union
            select leader from roundt
          )
   order by 1,2;
quit;