Updating SAS Data Sets

Handling Missing Values

If you update a master data set with a transaction data set, and the transaction data set contains missing values, you can use the UPDATEMODE option on the UPDATE statement to tell SAS how you want to handle the missing values. The UPDATEMODE option specifies whether missing values in a transaction data set will replace existing values in a master data set.

The syntax for using the UPDATEMODE option with the UPDATE statement is as follows:

UPDATE master-SAS-data-set transaction-SAS-data-set <UPDATEMODE=MISSINGCHECK | NOMISSINGCHECK>;

BY by-variable;

The MISSINGCHECK value in the UPDATEMODE option prevents missing values in a transaction data set from replacing values in a master data set. This is the default. The NOMISSINGCHECK value in the UPDATEMODE option enables missing values in a transaction data set to replace values in a master data set by preventing the check for missing data from being performed.

The following examples show how SAS handles missing values when you use the UPDATEMODE option on the UPDATE statement.

The following example creates and sorts a master data set:

options pagesize=60 linesize=80 pageno=1 nodate; 

data inventory;    
   input PartNumber $ Description $ Stock @17    
   ReceivedDate date9. @27 Price;
   format  ReceivedDate date9.;    
   datalines; 
K89R seal   34  27jul2004 245.00
M4J7 sander 98  20jun2004 45.88 
LK43 filter 121 19may2005 10.99 
MN21 brace 43   10aug2005 27.87 
BC85 clamp 80   16aug2005 9.55 
NCF3 valve 198  20mar2005 24.50 
;  

proc sort data=inventory;
   by PartNumber; 
run;  

proc print data=inventory;    
   title 'Master Data Set';
   title2 'Tool Warehouse Inventory'; 
run;

The following output shows the results:

The Master Data Set

                                Master Data Set                                1
                            Tool Warehouse Inventory

                  Part                              Received
          Obs    Number    Description    Stock         Date     Price

           1      BC85       clamp          80     16AUG2005      9.55
           2      K89R       seal           34     27JUL2004    245.00
           3      LK43       filter        121     19MAY2005     10.99
           4      M4J7       sander         98     20JUN2004     45.88
           5      MN21       brace          43     10AUG2005     27.87
           6      NCF3       valve         198     20MAR2005     24.50

The following example creates and sorts a transaction data set:

options linesize=80 pagesize=64 nodate pageno=1;     

data add_inventory;    
   input PartNumber $ 1-4 Description $ 6-11 Stock 13-15 @17 Price;    
   datalines; 
K89R seal       245.00
M4J7 sander 121  45.88 
LK43 filter 34  10.99 
MN21 brace      28.87 
BC85 clamp  57  11.64 
NCF3 valve  121    . 
;    

proc sort data=add_inventory;    
   by PartNumber; 
run;  

proc print data=add_inventory;
   title 'Transaction Data Set';    
   title2 'Tool Warehouse Inventory'; 
run;

The following output shows the results:

The Transaction Data Set

                              Transaction Data Set                             1
                            Tool Warehouse Inventory

                        Part
                Obs    Number    Description    Stock    Price

                 1      BC85       clamp          57     11.64
                 2      K89R       seal            .     245.00
                 3      LK43       filter         34     10.99
                 4      M4J7       sander        121     45.88
                 5      MN21       brace           .     28.87
                 6      NCF3       valve         121

In the following example, SAS uses the NOMISSINGCHECK value of the UPDATEMODE option on the UPDATE statement:

options pagesize=60 linesize=80 pageno=1 nodate; 
 
data new_inventory;  
update inventory add_inventory updatemode=nomissingcheck;    
   by PartNumber;   
   ReceivedDate=today();       
run;  

proc print data=new_inventory;
   title 'Updated Master Data Set';
   title2 'Tool Warehouse Inventory'; 
run;

The following output shows the results of using the NOMISSINGCHECK value. Observations 2 and 5 contain missing values for STOCK because the transaction data set contains missing values for STOCK for these items. Because checking for missing values in the transaction data set is not done, the original value in STOCK is replaced by missing values. In the sixth observation, the original value of PRICE is replaced by a missing value.

Updated Master Data Set: UPDATEMODE=NOMISSINGCHECK

                            Updated Master Data Set                            1
                            Tool Warehouse Inventory

                  Part                              Received
          Obs    Number    Description    Stock         Date     Price

           1      BC85       clamp          57     12JAN2007     11.64
           2      K89R       seal            .     12JAN2007    245.00
           3      LK43       filter         34     12JAN2007     10.99
           4      M4J7       sander        121     12JAN2007     45.88
           5      MN21       brace           .     12JAN2007     28.87
           6      NCF3       valve         121     12JAN2007       .

The following output shows the results of using the MISSINGCHECK value. Note that no missing values are written to the updated master data set. The missing data in observations 2, 5, and 6 of the transaction data set is ignored, and the original data from the master data set remains.

Updated Master Data Set: UPDATEMODE=MISSINGCHECK

                            Updated Master Data Set                            1
                            Tool Warehouse Inventory

                  Part                              Received
          Obs    Number    Description    Stock         Date     Price

           1      BC85       clamp          57     12JAN2007     11.64
           2      K89R       seal           34     12JAN2007    245.00
           3      LK43       filter         34     12JAN2007     10.99
           4      M4J7       sander        121     12JAN2007     45.88
           5      MN21       brace          43     12JAN2007     28.87
           6      NCF3       valve         121     12JAN2007     24.50

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

Top of Page