UPDATE Statement

Details

• Requirements
• Transaction Data Sets
• Missing Values

Requirements

• The UPDATE statement must be accompanied by a BY statement that specifies the variables by which observations are matched.
• The BY statement should immediately follow the UPDATE statement to which it applies.
• The data sets listed in the UPDATE statement must be sorted by the values of the variables listed in the BY statement, or they must have an appropriate index.
• Each observation in the master data set should have a unique value of the BY variable or BY variables. If there are multiple values for the BY variable, only the first observation with that value is updated. The transaction data set can contain more than one observation with the same BY value. (Multiple transaction observations are all applied to the master observation before it is written to the output file.)

For more information, see Examples: Prepare Data in SAS Programmer’s Guide: Essentials.

Transaction Data Sets

Usually, the master data set and the transaction data set contain the same variables. However, to reduce processing time, you can create a transaction data set that contains only those variables that are being updated. The transaction data set can also contain new variables to be added to the output data set.

The output data set contains one observation for each observation in the master data set. If any transaction observations do not match master observations, they become new observations in the output data set. Observations that are not to be updated can be omitted from the transaction data set. See Overview of Combining Data in SAS Programmer’s Guide: Essentials.

Missing Values

By default, the UPDATEMODE=MISSINGCHECK option is in effect, so missing values in the transaction data set do not replace existing values in the master data set. If you want missing values in the transaction data set to replace existing values in the master data set, specify the UPDATEMODE=NOMISSINGCHECK option in the UPDATE statement.

data newpay;
   update payroll increase updatemode=nomissingcheck;   
   by id;
run;

If you want only some variables that contain missing values to replace values from the master data set, you can specify the MISSING statementin the transaction data set. The MISSING statement enables you to assign characters in to represent special missing values.

• You specify the MISSING statement in the transaction data set to define a special character value and then replace the missing values with the special character defined in the MISSING statement.
• Valid special characters special character values for the MISSING statement are any single character A-Z, a-z, or the underscore character ( _ ). Even when UPDATEMODE=MISSINGCHECK is in effect, the missing values designated as special missing values in the transaction data set replace the original values from the master data set.
• SAS converts the letters a-z to upper case letters in the output data set.
• Special missing values designated with a letter A-Z or a-z appear with that letter in the output data set.
• Special missing values designated with an underscore ( _ ) appear as regular missing values in the output data set; that is, they are designated by a period ( . ) in the output data set.

data master; 
input Item $ Num;
datalines;
boot 12
shoe 10
sock 11
;
data trans;
input Item $ Num;
missing Z;
datalines;
boot  9
shoe  Z
sock 11
; 

data new;
update master trans; 
by Item;        
run;

data master;
input Item $ Num Grp $;
datalines;
boot 12 wnt
shoe 10 snk
sock 11 fab
;
data trans;
input Item $ Num Grp $;
missing _;
datalines;
boot  9  wnt
shoe 10   _
sock  _  fab
; 

data new;
update master trans;       
by Item;
run;

See Updating with Missing Values for another example that shows the use of special missing values.