sqlupdte.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLUPDTE                                            */
 /*   TITLE: DEMONSTRATES BASIC SQL UPDATE STATEMENTS            */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN UPDATE RESET UNDO_POLICY SET WHERE       */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS, PMK                    UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
  title1 '*** sqlupdte: basic SQL update statements ***';

  /*---------------------------------------------------------------*/
  /*-- The update statement updates SAS datasets in-place.       --*/
  /*-- The values for the updated variables can be specified as  --*/
  /*-- 1) constant values.                                       --*/
  /*-- 2) the results of an SQL subquery.                        --*/
  /*-- 3) expressions involving the original value.              --*/
  /*---------------------------------------------------------------*/


  /*---------------------------------------------------------------*/
  /*-- The first step is to create the Paper table 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
Jost            Foreign Language Issues     11:15  .
;


proc sql;


  /*---------------------------------------------------------------*/
  /*-- Jost does not have a section name.  Let's add "Users" as  --*/
  /*-- his section name.                                         --*/
  /*---------------------------------------------------------------*/

  update paper set section='Users' where author='Jost';

  title2 'After updating section for Jost';
  select * from paper;


  /*---------------------------------------------------------------*/
  /*-- Jost thinks his presentation will last about as long as   --*/
  /*-- everyone elses. Here we us an SQL update statement to     --*/
  /*-- set the duration for Josts paper to the average for all   --*/
  /*-- papers.                                                   --*/
  /*---------------------------------------------------------------*/

  /* The default value of the UNDO_POLICY option will
     cause PROC SQL to obtain exclusive access to the
     dataset being inserted into (sql.newprice).
     The second reference to the same table will fail.

     Choosing UNDO_POLICY=OPTIONAL allows this query to proceed.

     For details on the UNDO_POLICY option, please refer to the
     607 changes and enhancements documentation.                 */

  reset undo_policy=optional;

  update paper
     set duration = ( select avg(duration) from paper )
   where author = 'Jost';

  reset undo_policy=required;

  title2 'After updating duration for Jost to AVG(duration)';
  select * from paper where author='Jost';


  /*---------------------------------------------------------------*/
  /*-- Since everyone wants to sleep in, each paper will be      --*/
  /*-- presented 30 minutes later than originally scheduled.     --*/
  /*---------------------------------------------------------------*/
  update paper
     set time = time + '0:30't;

  title2 'After adding 30 minutes to the start times';
  select * from paper;
quit;