sqlview.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLVIEW                                             */
 /*   TITLE: Demonstrates SQL View Capabilities                  */
 /* PRODUCT: SAS                                                 */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: DATA MANAGEMENT, VIEWS                              */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS, PMK                    UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
  title1 '*** sqlview: basic SQL view ***';

  /*---------------------------------------------------------------*/
  /*-- The first step is to create the Paper table which will be --*/
  /*-- used in the following queries.                            --*/
  /*---------------------------------------------------------------*/

title2 'Papers Presented';

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
;


proc sql;


  /*---------------------------------------------------------------*/
  /*-- We create a view to add a column to the raw data -- here  --*/
  /*-- we compute the endtime based on starttime and duration.   --*/
  /*---------------------------------------------------------------*/

  create view pt as
  select author, title, time, duration label='Duration',
         time + duration*60 as endtime format=time5.
    from paper;


  /*---------------------------------------------------------------*/
  /*-- PROC SQL can use views as if they were datasets.          --*/
  /*---------------------------------------------------------------*/

  select * from pt
   where endtime > '14:00't;


  /*---------------------------------------------------------------*/
  /*-- Let's make a list and timeline of papers to be presented. --*/
  /*-- Notice than SAS procedures can access SQL views as if     --*/
  /*-- they were SAS datasets as well.                           --*/
  /*---------------------------------------------------------------*/

proc print data=pt;
  run;

proc timeplot data=pt;
  plot time='<' endtime='>' / overlay ref='12:00't hiloc;
  class author;
  run;


  /*---------------------------------------------------------------*/
  /*-- What is the order in which the papers will be presented?  --*/
  /*-- Notice than we can have views that are based on others    --*/
  /*---------------------------------------------------------------*/

proc sql;
   create view pt_time as
   select * from pt order by time;


proc print data=pt_time;
  run;


  /*---------------------------------------------------------------*/
  /*-- Does this new order effect the timeline?                  --*/
  /*---------------------------------------------------------------*/

proc timeplot data=pt_time;
   plot time='<' endtime='>' / overlay ref='12:00't hiloc;
   class author;
   run;