Chapter Contents

Previous

Next
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:


Chapter Contents

Previous

Next

Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.