Transactional Updates with NOMISSINGCHECK
If we need to update a master table with a transaction table an UPDATE statement can be appropriate. The default behavior of the UPDATE statement is to ignore any of the missing values in the transaction data set so not to overwrite populated values during the update.
If we want to change this behavior and replace existing values with missing values, we need to specify the NOMISSINGCHECK option. The general syntax for this is shown below:
data master; update master transactions updatemode=nomissingcheck; by keys; run;
Consider the following worked example. The MASTER table initially has three customers with a value.
TRANSACTIONS table is then provided that sets the value for customer 2 to missing and appends a new customer.
data master; input customer value; datalines; 1 12 2 36 3 24 run;
data transactions; input customer value; datalines; 2 . 4 48 run;
data master; update master transactions updatemode=nomissingcheck; by customer; run; proc print; run;
The output produced is shown below:
Obs customer value
1 1 12 2 2 . 3 3 24 4 4 48
Without the NOMISSINGCHECK, the value for Customer 2 remains 36. Note, in practice the two tables should be sorted or indexed before performing the data step.