/****************************************************************/ /*          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 35Jerry   Testing Involving Users              9:50 30Nick    Testing Plan to test, test to plan  10:30 20Peter   Info SysArtificial Intelligence      9:30 45Paul    Info SysQuery Languages             10:30 40Lewis   Info SysQuery Optimisers            15:30 25Jonas   Users   Starting a Local User Group 14:30 35Jim     Users   Keeping power users happy   15:15 20Janet   Users   Keeping everyone informed   15:45 30Marti   GraphicsMulti-dimensional graphics  16:30 35Marge   GraphicsMake your own point!        15:10 35Mike    GraphicsMaking do without color     15:50 15Jane    GraphicsPrimary colors, use em!     16:15 25;data section; input section$1-8 room$ convenor$; cards;Graphics  Sable  DeniseInfo Sys  Kudu   PeterTesting   Sable  JennyUsers     Kudu   Sally;data roundt; input leader$1-8 subject$9-30; label subject='Roundtable Subject'; cards;Mary    External DBMS'sNick    Testing NetworksJerry   User SpecificationsPeter   Selling SolutionsJim     Distasteful JokesMarge   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;