Chapter Contents |
Previous |
Next |
SAS/ACCESS Software for Relational Databases: Reference |
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.