sqldelte.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLDELTE                                            */
 /*   TITLE: DEMONSTRATES BASIC SQL DELETE STATEMENTS            */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN INSERT WHERE DELETE SELECT MIN           */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: KMS, PMK                    UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC:                                                     */
 /*                                                              */
 /****************************************************************/
  title1 '*** sqldelte: basic SQL delete statements ***';


proc sql;


  /*---------------------------------------------------------------*/
  /*-- Let's create a table that we can use to demonstrating     --*/
  /*-- adding and deleting data.                                 --*/
  /*---------------------------------------------------------------*/

  create table counts( section char(20), papers num );

  insert into counts values('Graphics', 4)
                     values('Info Sys', 3)
                     values('Testing',  2)
                     values('Users',    3)
                     values('',         1)    ;

  title2 ' Before Deleting from Counts';
  select * from counts;



  /*---------------------------------------------------------------*/
  /*-- delete papers that have no value for section variable.    --*/
  /*-- the where clause defines which rows are to be deleted.    --*/
  /*---------------------------------------------------------------*/
  delete from counts
   where section is null;

  title2 'After Deleting where section is null';
  select * from counts;


  /*---------------------------------------------------------------*/
  /*-- There are too many papers being presented.  The sections  --*/
  /*-- with the least number of papers will be eliminated.       --*/
  /*-- The Where clause may contain a subquery.                  --*/
  /*---------------------------------------------------------------*/
  delete from counts
   where papers = ( select min(papers) from counts );

  title2 'After deleting sections with fewest papers';
  select * from counts;
quit;