SAS/ACCESS Software for PC File Formats: Reference

Updating PC Files Data with the MODIFY Statement

The MODIFY statement extends the capabilities of the DATA step by enabling you to modify DBF file 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 DBF file.

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 ORDERS.DBF file. You also create a transaction data file, DLIB.TRANS, that you use to update the master data set (and therefore, the ORDERS.DBF 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.DBF table with data from the DLIB.TRANS data file. The SAS System reads one observation (or row) of the ORDERS.DBF 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 dmbs=dbf;
   /* create access descriptor  */
   create adlib.orders.access;      
   
   path="c:\sasdemo\orders.dbf";
   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;
   ordernum=12102;     
   /*Obs. 1 specifies Update for 
     ORDERNUM=12102*/
      shipped='05DEC1998'd;
      type='U';
      output;

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

   ordernum=13000;     
   /*Obs. 3 specifies Add for new 
     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;

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

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

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

proc print data=vlib.master;
   where ordernum in(12102 12160 13000 12465);
   title 'DBF File 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 that it displays only the observations that are included in the transaction data set. 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 PC Files Data with a MODIFY Statement.

Revising PC Files Data with a MODIFY Statement

DBF File Data Updated with the MODIFY Statement OBS DELETE_F ORDERNUM STOCKNUM LENGTH FABCHARG SHIPTO DATEORD SHIPPED TAKENBY PROCESBY 22 12102 8934 110 11063836.00 18543489 15NOV1998 05DEC1998 456910 . 23 12160 3478 1000 . 29834248 19NOV1998 . 456910 . 26 * 12465 3478 1000 . 29834248 23DEC1998 . 234967 . 39 13000 9870 650 . 19876078 18JAN1999 29JAN1999 321783 120591 OBS SPECINST 22 23 Customer agrees to pay in full. 26 39 Customer agrees to certain limitations.

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 05DEC1998. The second observation sets the value to missing. If the value of SHIPPED was not set to missing in the second observation, the value 05DEC1998 would be incorrectly supplied. Therefore, you may want to create your transaction data set in a particular order to minimize having to reset variables.

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

You cannot use the POINT= option because observation numbers are not available in a DBF file.
The NOBS= option displays the largest positive integer value available on the host operating system.
Each PC files statement that is issued, whether an INSERT, DELETE, or UPDATE, is a separate transaction and is saved in the DBF file. You cannot undo (or reverse) these changes without re-editing.

For more information about the MODIFY statement, see SAS Language Reference: Dictionary.

Chapter Contents
Previous
Next
Top of Page