sqljoin.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLJOIN                                             */
 /*   TITLE: DEMONSTRATES BASIC SQL JOIN FEATURES                */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN FULL JOIN WHERE SELECT COALESCE BETWEEN  */
 /*          ORDER BY                                            */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS, PMK                    UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
  title1 '*** sqlinsrt: basic SQL join statements ***';

  /*---------------------------------------------------------------*/
  /*-- 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 are also roundtable leaders?                 --*/
  /*---------------------------------------------------------------*/

  title2 'Papers and Roundtables';
  select author, title, subject
    from paper, roundt
   where author = leader;


  /*---------------------------------------------------------------*/
  /*-- What papers and roundtable subjects will be discussed?    --*/
  /*-- The previous statement excludes those people that do not  --*/
  /*-- present a paper AS WELL AS lead a roundtable luncheon.    --*/
  /*---------------------------------------------------------------*/

  title2 'Paper and Roundtable Discussions';
  select author, title, subject
    from paper full join roundt
      on author = leader;


  /*---------------------------------------------------------------*/
  /*-- What papers and roundtable subjects will be discussed and --*/
  /*-- who is responsible for presenting them?                   --*/
  /*--                                                           --*/
  /*-- This is an improvement over the previous query as it      --*/
  /*-- combines the author/leader values into one column.        --*/
  /*---------------------------------------------------------------*/

  title2 "What's being presented and who's responsible?";
  select coalesce(author, leader) as person, title, subject
    from paper full join roundt
      on author = leader;







  /*---------------------------------------------------------------*/
  /*-- Whose papers are presented after this one ends?           --*/
  /*--                                                           --*/
  /*-- This demonstrates:                                        --*/
  /*--    1) joining a table to another "instance" of itself     --*/
  /*--    2) using a condition other than "=" in the join clause --*/
  /*--       here were are interested in a range of matches.     --*/
  /*--                                                           --*/
  /*-- The aliases given to the two instances of the paper       --*/
  /*-- table are p (for previous)  and n (for next)              --*/
  /*---------------------------------------------------------------*/

  title2 'Papers to try next';
  select p.author label='Just Heard',
         n.author label='Then Try', n.title, n.section, n.time
    from paper p, paper n
   where n.time between p.time + p.duration*60
                    and p.time + (30+p.duration)*60
   order by p.author;




  /*---------------------------------------------------------------*/
  /*-- Who is a very busy person and is giving a paper, leading  --*/
  /*-- a round table and chairing a section?                     --*/
  /*--                                                           --*/
  /*-- This demonstrates a join involving more than two tables   --*/
  /*-- and using the DISTINCT keyword to eliminate duplicates.   --*/
  /*---------------------------------------------------------------*/

  title2 'Very Busy Persons';
  select distinct author
    from paper, section, roundt
   where author=convenor and author=leader;



  /*---------------------------------------------------------------*/
  /*-- A list is needed to post in the lobby that outlines the   --*/
  /*-- topics of papers and roundtable discussions and who is    --*/
  /*-- responsible for them.                                     --*/
  /*---------------------------------------------------------------*/

  title2 'Composite Roster';
  select coalesce(author,convenor,leader) as person,
         title label='Gives Paper:',
         section.section label='Convenes Section:',
         subject label='Leads Roundtable on:'
    from paper full join section on author=convenor
               full join roundt on coalesce(author, convenor)=leader
   order by 1;
quit;