There are times when you want to modify the observations in a SAS data set without replacing the data set. You can do this in a DATA step with the MODIFY statement. Using the MODIFY statement, you can replace, delete, or append observations in an existing data set without creating an additional copy of the data. In this chapter, you learn to modify all the observations in a data set, matching observations using a BY statement, and observations located using an index.

When you modify data, it's often essential to safeguard your data and track the changes that are made. In this chapter you learn how to create integrity constraints to protect your data. You will also learn two different methods of tracking changes — audit trails and generation data sets. You use audit trails to track changes that are made to a data set in place, and you use generation data sets to track changes that are made when the data set is rebuilt.

In this chapter, you learn to

Suppose an airline has decided to give passengers more leg room. To do so the airline must decrease the number of seats in the business and economy classes. The SAS data set Capacity has the variables CapEcon and CapBusiness that hold values for the number of seats in the economy and business classes.

In the program below, the assignment statement for CapEcon reduces the number of seats in the economy class to 95% of the original number, and the assignment statement for CapBusiness reduces the number of seats in the business class to 90% of the original number. The INT function is used in both assignment statements to return the integer portion of the result.

   proc print data=capacity (obs=4);
   run;


   data capacity;
      modify capacity;
      CapEcon = int(CapEcon * .95);
      CapBusiness = int(CapBusiness * .90);
   run;


   proc print data=capacity (obs=4);
   run;

The following output shows the data before the MODIFY statement.

The following output shows the data after the MODIFY statement. You can see that the values in CapBusiness and CapEcon have been reduced.

Suppose you have a master data set, Capacity, which has route numbers for an airline. Some of the route numbers have changed, and the changes are stored in a transaction data set, Newrtnum. The master data set is updated by matching values of the variable FlightID.

proc print data=capacity(obs=5);
run;

data capacity;
   modify capacity sasuser.newrtnum;
   by flightid;
run;

proc print data=capacity(obs=5);
run;

The following PROC PRINT output displays the first five rows of the data set Capacity before updates were applied.

As you can see in this PROC PRINT output, three values of FlightID in Newrtnum have matches in Capacity. For each matching observation, the values for RouteID are updated.

When you use the MODIFY and BY statements to update a data set, WHERE processing starts at the top of the master data set and finds the first match and updates it. We will consider what happens if there are duplicate values in the master or transaction data sets. Suppose you have the following code to make updates to the master data set M using the transaction data set T:

data m;
    modify m t;
    by a;
run;

If duplicate values of the BY variable exist in the master data set, only the first observation in the group of duplicate values is updated because WHERE processing begins at the top of the data set and updates the first match.

If duplicate values of the BY variable exist in the transaction data set, the duplicate values overwrite each other so that the last value in the group of duplicate transaction values is the result in the master data set.

You can avoid overwriting duplicate values by writing an accumulation statement so that all the observations in the transaction data set are added to the master observation.

If there are missing values in the transaction data set, SAS does not replace the data in the master data set with missing values unless they are special missing values.

You can specify how missing values in the transaction data set are handled by using the UPDATEMODE= option in the MODIFY statement.

Suppose that airline cargo weights for 1999 are stored in the master data set Cargo99, which has a composite index named FlghtDte on the variables FlightID and Date. Some of the data is incorrect and the data set needs to be updated. The correct cargo data is stored in the transaction data set Newcgnum.

In the program below, the KEY= option specifies the FlghtDte index. When a matching observation is found in Cargo99, three variables (CapCargo, CargoWgt, and CargoRev) are updated.

   proc print data=cargo99(obs=5);
   run;

   data cargo99;
      set sasuser.newcgnum (rename =
          (capcargo = newCapCargo
          cargowgt = newCargoWgt
          cargorev = newCargoRev));
      modify cargo99 key=flghtdte; 
      capcargo = newcapcargo;
      cargowgt = newcargowgt;
      cargorev = newcargorev;
   run;

   proc print data=cargo99(obs=5);
   run;

The output below shows the first five observations of the SAS data set Cargo99 before it was modified by Newcgnum.

The output below shows the first five observations of the SAS data set Cargo99 after it was modified by Newcgnum. Notice that the three variables in the first observation were updated by the values in Newcgnum.

When you use an index to locate values to update, duplicate values of the indexed variable in the transaction data set might cause problems. We consider what happens with various scenarios when you use the following code to update the master data set M with values from the transaction data set T. The index on the M data set is built on the variable A:

data m;
   set t (rename=(b=newb));
   modify m key=a;
   b=newb;
run;

If there are duplications in the master data set, only the first occurrence is updated.

If there are nonconsecutive duplications in the transaction data set, SAS updates the first observation in the master data set. The last duplicate transaction value is the result in the master data set after the update.

If there are consecutive duplications in the transaction data set, some of which do not have a match in the master data set, then SAS performs a one-to-one update until it finds a non-match. At that time, the DATA step terminates with an error.

Adding the UNIQUE option to the MODIFY statement allows you to avoid the error in the DATA step. The UNIQUE option causes the DATA step to return to the top of the index each time it looks for a match for the value from the transaction data set. The UNIQUE option can be used only with the KEY= option.

You can specify the UNIQUE option to

When you use the UNIQUE option and there are consecutive duplications in the transaction data set, SAS updates the first observation in the master data set. This is similar to what happens when you have nonconsecutive duplications in the transaction data set. If the values in the transaction data set should be added to the value in the master data set, you can write a statement to accumulate the values from all the duplicates.

If the SAS data set Transaction has a variable named code having values of yes, no, and new, you can submit the following program to

data master;
   set transaction;
   modify master key = id;
   a = b;
   if code = 'no' then remove;
   else  if code = 'yes' then replace;
   else  if code = 'new' then output;
run;

When you use the MODIFY statement with a BY statement or KEY= option to update a data set, error checking is important for several reasons. The most important reason is that these tools use nonsequential access methods, so there is no guarantee that an observation will be located that satisfies the request. Error checking enables you to perform updates or not, depending on the outcome of the I/O condition.

The automatic variable _IORC_ (Input Output Return Code) is created when you use the MODIFY statement with the BY statement or KEY= option. The value of _IORC_ is a numeric return code that indicates the status of the most recently executed I/O operation. Checking the value of this variable allows you to detect abnormal I/O conditions and direct execution in particular ways rather than having the application terminate abnormally.

Because the values of the _IORC_ automatic variable are internal and subject to change, %SYSRC, an autocall macro, was created to enable you to test for specific I/O conditions while protecting your code from future changes in _IORC_ values.

When you use %SYSRC, you can check the value of _IORC_ by specifying one of the mnemonics listed in the table below.

Suppose you are using the MODIFY statement with the KEY= option to update a SAS data set. In the program below, when _IORC_ has the value _SOK, the observation is updated. When _IORC_ has the value _DSENOM, no matching observation is found, so the observation is appended to the data set by the OUTPUT statement and _ERROR_ is reset to 0 in the do loop.

data master;
   set transaction;
   modify master key = id;
   if _IORC_=%sysrc(_sok) then
      do;
         a = b;
         replace;
      end;
   else
      if _IORC_=%sysrc(_dsenom) then
         do;
            output;
            _ERROR_ = 0;
         end;
run;

General integrity constraints enable you to restrict the values of variables within a single data set. The following four integrity constraints can be used as general integrity constraints:

Referential constraints enable you to link the data values of a column in one data set to the data values of columns in another data set. You create a referential integrity constraint when a FOREIGN KEY integrity constraint in one data set references a PRIMARY KEY integrity constraint in another data set. To create a referential integrity constraint, you must follow two steps:

Suppose you have a data set that contains route information and passenger capacity for each class in an airline. You need to create integrity constraints to ensure that when the data set is updated

In the code below, the IC CREATE statement is used to create two general integrity constraints on variables in the data set Capinfo:

proc datasets nolist;
   modify capinfo;
   ic create PKIDInfo=primary key(routeid)
      message='You must supply a Route ID Number';
   ic create Class1=check(where=(cap1st<capbusiness or capbusiness=.))
      message='Cap1st must be less than CapBusiness';
quit;

Notice that the NOLIST option is used to prevent a listing of the Work directory that PROC DATASETS generally produces. When the constraint is created, a message is written to the SAS log.

Table 18.1. SAS Log

Once integrity constraints are in place, SAS enforces them whenever you modify values in the data set in place. Techniques for modifying data in place include using

The code in the previous example placed a check constraint on Cap1st and CapBusiness to ensure that values for the capacity in business class were either greater than first class or missing. Suppose that you ran the following program to triple the capacity in first class. This would probably violate the check constraint for some observations.

data capinfo;
   modify capinfo;
   cap1st=cap1st*3;
run;

The observations that failed to pass the integrity constraint are written to the SAS log. As you can see, all these observations would have had values of Cap1st greater than those of CapBusiness.

Table 18.2. SAS Log

If you used the VIEWTABLE window or another window to make this update, SAS would have displayed the error message defined for the integrity constraint.

The APPEND, COPY, CPORT, CIMPORT, and SORT procedures preserve integrity constraints when their operation results in a copy of the original data file. Integrity constraints are also preserved if you copy a data set using the SAS Explorer window.

The following code displays information about the Capinfo data set, including the integrity constraints that were added to this data set in the last example. Notice that the NOLIST option is used here to suppress the listing of all data sets in the Work library. With this option, only the information for the Capinfo data set is listed.

proc datasets nolist;
   contents data=capinfo;
quit;

Only the integrity constraints portion of the output is shown below.

The code below removes the integrity constraints on the Capinfo data set:

  proc datasets;
      modify capinfo;
      ic delete pkidinfo;
      ic delete class1;
   quit;

A message is written to the SAS log when the integrity constraint is deleted.

Table 18.3. SAS Log

The following code initiates an audit trail on the data set Capinfo.

proc datasets nolist;
   audit capinfo;
   initiate;
quit;
Δ

Table 18.4. SAS Log

When the audit trail is initiated, it has no observations until the first modification is made to the audited data set. When the audit trail file contains data, you can read it with any component of SAS that reads a data set. To refer to the audit trail file, use the TYPE= data set option.

The following PROC CONTENTS code displays the contents of the audit trial file:

proc contents data=mylib.sales (type=audit);
run;

The following PROC PRINT code lists the data in the audit trail file for the data set Capinfo:

proc print data=capinfo (type=audit);
run;

As you might expect, the audit trail file has the same set of variables that are in the audited data set. If the data set contains the variables A and B, the variables A and B are also in the audit trail file.

Next consider the audit trail variables that automatically store information about changes that you make to the data.

Audit trail variables automatically store information about data modifications. Audit trail variable names begin with AT followed by a specific string, such as DATETIME.

The _ATOPCODE_ variable contains a code that describes the type of operation that wrote the observation to the audit file. For example, if you modified all observations in an audited data set, the audit file would contain twice as many observations as the original data set. The audit file would contain one observation that matched the original observation with an _ATOPCODE_ value of DR, and one updated observation with an _ATOPCODE_ value of DW.

The table below shows the possible values of the _ATOPCODE_ variable.

You can define what information is stored in the audit file by using the LOG statement when you initiate the audit trail.

When you initiate an audit trail, options in the LOG statement determine the type of entries stored in the audit trail, along with their corresponding _ATOPCODE_ values. The ERROR_IMAGE option controls E operation codes. The BEFORE_IMAGE option controls the DR operation code, and the DATA_IMAGE option controls all other D operation codes. If you omit the LOG statement when you initiate the audit trail, the default behavior is to log all images.

The following code initiates an audit trail on the data set Capinfo but stores only error record images. This means that the audit file will contain only records where an error occurred. The _ATOPCODE_ values can be only EA, ED, and EU.

proc datasets nolist;
   audit capinfo;
   initiate;
   log data_image=NO before_image=NO;
quit;
Δ

User variables allow the person editing the file to enter information about changes they are making to the data. Although the data values are stored in the audit file, you can update them in the data set like any other variable.

User variables are created by using the USER_VAR statement in the audit trail specification.

User variables are unique in SAS in that they are stored in one file (the audit file) and opened for update in another file (the data set). When the data set is opened for update, the user variables display, and you can edit them as if they are part of the data set.

Suppose you must monitor the updates for the data set Capinfo. The following code initiates an audit trail for the data set Capinfo and creates two user variables, who and why, to store who made changes to the data set and why the changes were made.

proc datasets nolist;
   audit capinfo;
   initiate;
   user_var who $20 label = 'Who made the change'
            why $20 label = 'Why the change was made';
quit;
Δ

Once these user variables are set up, they are retrieved from the audit trail and displayed when the data set is opened for update. You can enter data values for the user variables as you would for any data variable. The data values are saved to the audit trail as each observation is saved. The user variables are not available when the data is opened for browsing or printing. To rename a user variable or modify its attributes, you modify the data set, not the audit file.

The following code terminates the audit trail on the data set Capinfo.

proc datasets nolist;
   audit capinfo;
   terminate;
quit;

Δ

A message is written to the log when the audit trail is terminated.

Table 18.5. SAS Log

The following DATASETS procedure modifies the data set Cargorev and requests that up to four versions be kept (one base version and three historical versions).

proc datasets nolist;
   modify cargorev (genmax=4);
quit;

Δ

No message is written to the log when you specify the GENMAX= option.

Remember, new versions of a generation data set are created only when a data set is replaced, not when it is modified in place. To create new generations, use

To print the current version of the data, you do not need to use the GENNUM= option. Simply use

proc print data=year;
run;

To print the youngest historical version, you can either specify the relative or absolute reference on the GENNUM= option, as shown:

proc print data=year(gennum=4);   /*absolute reference*/
run;

or

proc print data=year(gennum=-1);   /*relative reference*/
run;

You can also view information about a specific generation using the GENNUM= option with PROC CONTENTS, as shown:

proc contents data=year(gennum=-1);   /*relative reference*/
run;

Now that you have seen a few examples of using the GENNUM= option, consider how generation numbers change.

When you use the GENNUM= option, you can refer to either the absolute or relative generation number. It's helpful to understand how generation numbers change so that you can identify the generation you want to process.

First, consider how SAS names generation data sets. The first time a data set with generations in effect is replaced, SAS keeps the replaced data set and appends a four-character version number to its member name, which includes the pound symbol (#) and a three-digit number. That is, for a data set named A, the replaced data set becomes A#001. When the data set is replaced for the second time, the replaced data set becomes A#002. That is, A#002 is the version that is chronologically closest to the base version. The table below shows the result after three replacements.

The limit for version numbers that SAS can append is #999. After 1000 replacements, SAS rolls over the youngest version number to #000.

Now we will consider how absolute and relative generation numbers (specified on the GENNUM= option) change. Each time SAS creates a new generation, the absolute generation number increases sequentially. As older generations are deleted, their absolute generation numbers are retired.

In contrast, the relative generation number always refers to generations in relation to the base generation. The base or current generation is always 0 and -1 is the youngest historical version.

The following table shows data set names and their absolute and relative GENNUM= numbers.

You have learned that you use PROC DATASETS to initiate generation data sets on an existing SAS data set. Once you have created generation data sets, you can use PROC DATASETS to perform management tasks such as

The following code uses the CHANGE statement to rename the data set SalesData to Sales. If generations have been created, the base name of all generations will be renamed.

proc datasets library=quarter1 nolist;
   change salesData=sales; 
quit;

The following code uses the GENNUM= option to rename only the second historical data set:

proc datasets library=quarter1 nolist;
   change sales(gennum=2)=newsales; 
quit;

The following code deletes one historical version. This action might leave a hole in the generation group.

proc datasets library=quarter1 nolist;
   delete newsales(gennum=-1); 
quit;

When you use the GENNUM= option with the DELETE statement, you can use the HIST and ALL keywords. The following code uses the HIST keyword to delete all of the historical versions:

proc datasets library=quarter1 nolist;
   delete newsales(gennum=HIST); 
quit;

The following code uses the ALL keyword in the GENNUM= option to delete all of the SAS data sets in a generation group:

proc datasets library=quarter1 nolist;
   delete newsales(gennum=ALL); 
quit;