SAS/ACCESS Software for Relational Databases: Reference

Updating DBMS Data with the MODIFY Statement

The MODIFY statement extends the capabilities of the DATA step by enabling you to modify data accessed by a view descriptor or a SAS data file without creating an additional copy of the data. To use the MODIFY statement with a view descriptor, you must have UPDATE privileges on the view's underlying DBMS table.

A DBMS trigger may prevent you from modifying observations in a DBMS table. Refer to your DBMS documentation to see if triggers are used in your DBMS.

You can specify either a view descriptor or a SAS data file as the master data set in the MODIFY statement. In the following example, the master data set is the view descriptor VLIB.MASTER, which describes data in the ORACLE table ORDERS. You also create a transaction data file, DLIB.TRANS, that you use to update the master data set (and therefore, the ORDERS table). The SAS variable names, formats, and informats of the transaction data file must correspond to those described by the view descriptor VLIB.MASTER.

Using the VLIB.MASTER view descriptor, the MODIFY statement updates the ORDERS table with data from the DLIB.TRANS data file. The SAS System reads one observation (or row) of the ORDERS table for each iteration of the DATA step, and performs any operations that the code specifies. In this case, the IF-THEN statements specify whether the information for an order is to be updated, added, or deleted.

proc access dbms=oracle;
/* create access descriptor  */
   create adlib.orders.access;
   user=scott;  orapw=tiger;
   path='myorapath';
   table=orders;
   assign=yes;
   rename dateorderd = dateord
          processdby = procesby;
   format dateorderd date9.
          shipped date9.
          ordernum      5.0
          length        4.0
          stocknum      4.0
          takenby       6.0
          processdby    6.0
          fabcharges    12.2;

/* create vlib.master view   */
   create vlib.master.view;
   select all;
run;

data dlib.trans;
/* Obs. 1 specifies Update for */
/* ORDERNUM=12102              */
   ordernum=12102;
      shipped='05DEC1998'd;
      type='U';
      output;

/* Obs. 2 specifies Update for */
/* ORDERNUM=12160              */
   ordernum=12160;
      shipped=.;
      takenby=456910;
      type='U';
      output;

/* Obs. 3 specifies Add for new */
/* ORDERNUM=13000               */
   ordernum=13000;
      stocknum=9870;
      length=650;
      fabcharg=.;
      shipto='19876078';
      dateord='18JAN1999'd;
      shipped='29JAN1999'd;
      takenby=321783;
      procesby=120591;
      specinst='Customer agrees to 
                certain limitations.';
      type='A';
      output;

/* Obs. 4 specifies Delete for */
/* ORDERNUM=12465              */
   ordernum=12465;
      type='D';
      output;
 run;

/* MODIFY statement example */
data vlib.master;
   modify vlib.master dlib.trans;
   by ordernum;
   select (_iorc_);
/* No match in MASTER - Add */
      when (%sysrc(_dsenmr)) do;
         if type='A' 
            then output vlib.master;
         _error_ = 0;
      end;
/* Match located - Update or Delete */
      when (%sysrc(_sok)) do;
         if type='U' 
            then replace vlib.master;
         else if type='D' 
            then remove vlib.master;
      end;
/* Traps unexpected outcomes */
      otherwise do;
      put 'Unexpected ERROR condition: 
          _IORC_ = ' _iorc_ ;
/* This dumps all vars in the PDV */
         put _all_;
         _error_ = 0;
      end;
   end;
run;

/* prints the example's output */
options linesize=120;

proc print data=vlib.master;
   where ordernum 
      in(12102 12160 13000 12465);
   title 'ORACLE Data Updated with 
         the MODIFY Statement';
run;

The DATA step uses the SYSRC macro to check the value of the _IORC_ automatic variable. It also prevents an error message from being generated when no match is found in the VLIB.MASTER file for an observation that is being added. It prevents the error message by resetting the _ERROR_ automatic variable to 0. The PRINT procedure specifies a WHERE statement so only the observations that are included in the transaction data set are displayed. The observation with ORDERNUM 12465 is deleted by the MODIFY statement, so it does not appear in the results. The results of this example are shown in Revising DBMS Data with a MODIFY Statement.

Revising DBMS Data with a MODIFY Statement

DBMS Data Updated with the MODIFY Statement OBS ORDERNUM STOCKNUM LENGTH FABCHARG SHIPTO DATEORD SHIPPED TAKENBY PROCESBY 1 13000 9870 650 . 19876078 18JAN1999 29JAN1999 321783 120591 2 12160 3478 1000 . 29834248 19NOV1998 . 456910 . 3 12102 8934 110 11063836.00 18543489 15NOV1998 05DEC1998 456910 . OBS SPECINST 1 Customer agrees to certain limitations. 2 Customer agrees to pay in full.

In this example, any column value that you specify in the transaction data set carries over to any subsequent observations if the values for the subsequent observations are missing. For example, the first observation sets the value of SHIPPED to 05DEC98. The second observation sets the value to MISSING. If the value of SHIPPED were not set to MISSING in the second observation, the value 05DEC98 would be incorrectly supplied. Therefore, you might want to create your transaction data set in a specific order to minimize having to reset variables.

There are some differences in the ways you use a MODIFY statement to update a SAS data file and to update DBMS data through a view descriptor. When a view descriptor is used as the master data set in a MODIFY statement, the following conditions apply:

The POINT= option cannot be used because observation numbers are not available in a relational DBMS table.
The NOBS= option displays the largest positive integer value available on the host operating system.
The KEY= option cannot be used because the DBMS determines whether to use DBMS indexes that have been assigned to columns in tables.
Each DBMS statement that is issued, whether an INSERT, DELETE, or UPDATE, is a separate transaction and is saved in the DBMS table. You cannot undo (or reverse) these changes without re-editing.

Chapter Contents
Previous
Next
Top of Page