Output 21.18 Results of Updating a Master Data Set by Adding Observations
In the example, the DATA step updates the master data set, Inventory, based on values in
the transaction data set, Add_Inventory. The program uses the OUTPUT statement to
add observations 2, 3, and 5 to the master data set. It also uses the REPLACE statement
to update observations 1, 4, and 6 with new values for InStock. The _IORC_ automatic
variable is used for error checking.
For more information about _IORC_ and error checking, see “Error Checking When
Using Indexes to Randomly Access or Update Data” on page 507. For information
about using the SELECT statement conditionally, see “SELECT Statement” in SAS
Statements: Reference.
Error Checking When Using Indexes to Randomly
Access or Update Data
The Importance of Error Checking
When reading observations with the SET statement and KEY= option or with the
MODIFY statement, error checking is imperative for several reasons. The most
important reason is that these tools use nonsequential access methods. Therefore, there is
no guarantee that an observation will be located that satisfies the request. Error checking
enables you to direct execution to specific code paths, depending on the outcome of the
I/O operation. Your program continues execution for expected conditions and terminate
execution when unexpected results occur.
Error Checking When Using Indexes to Randomly Access or Update Data 507
Error-Checking Tools
Two tools have been created to make error checking easier when you use the MODIFY
statement or the SET statement with the KEY= option to process SAS data sets:
• _IORC_ automatic variable
• SYSRC autocall macro
_IORC_ is created automatically when you use the MODIFY statement or the SET
statement with KEY=. The value of _IORC_ is a numeric return code that indicates the
status of the I/O operation from the most recently executed MODIFY or SET statement
with KEY=. Checking the value of this variable enables you to detect abnormal I/O
conditions and to direct execution down specific code paths instead of having the
application terminate abnormally. For example, if the KEY= variable value does match
between two observations, you might want to combine them and output an observation.
If they do not match, however, you might want to only write a note to the log.
Because the values of the _IORC_ automatic variable are internal and subject to change,
the SYSRC 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
following table.
Table 21.4 Most Common Mnemonic Values of _IORC_ for DATA Step Processing
Mnemonic Value Meaning of Return Code When Return Code Occurs
_DSENMR The Transaction data set
observation does not exist in the
Master data set.
MODIFY with BY is used and no
match occurs.
_DSEMTR Multiple Transaction data set
observations with the same BY
variable value do not exist in the
Master data set.
MODIFY with BY is used and
consecutive observations with the
same BY values do not find a
match in the first data set. In this
situation, the first observation that
fails to find a match returns
_DSENMR. The subsequent
observations return _DSEMTR.
_DSENOM No matching observation was
found in the Master data set.
SET or MODIFY with KEY=
finds no match.
_SENOCHN The output operation was
unsuccessful.
the KEY= option in a MODIFY
statement contains duplicate
values.
_SOK The I/O operation was successful. a match is found.
508 Chapter 21 • Reading, Combining, and Modifying SAS Data Sets
Example 1: Routing Execution When an Unexpected Condition
Occurs
Overview
This example shows how to prevent an unexpected condition from terminating the
DATA step. The goal is to update a master data set with new information from a
transaction data set. This application assumes that there are no duplicate values for the
common variable in either data set.
Note: This program works as expected only if the master and transaction data sets
contain no consecutive observations with the same value for the common variable.
For an explanation of the behavior of MODIFY with KEY= when duplicates exist,
see the MODIFY statement in SAS Statements: Reference.
Input Data Sets
The Transaction data set contains three observations: two updates to information in
Master and a new observation about PartNumber value 6 that needs to be added. Master
is indexed on PartNumber. There are no duplicate values of PartNumber in Master or
Transaction. The following shows the Master and the Transaction input data sets:
Master Transaction
OBS PartNumber Quantity OBS PartNumber AddQuantity
1 1 10 1 4 14
2 2 20 2 6 16
3 3 30 3 2 12
4 4 40
5 5 50
Original Program
The objective is to update the Master data set with information from the Transaction data
set. The program reads Transaction sequentially. Master is read directly, not sequentially,
using the MODIFY statement and the KEY= option. Only observations with matching
values for PartNumber, which is the KEY= variable, are read from Master.
data master; 1
set transaction; 2
modify master key=PartNumber; 3
Quantity = Quantity + AddQuantity; 4
run;
1
Open the Master data set for update.
2
Read an observation from the Transaction data set.
3
Match observations from the Master data set based on the values of PartNumber.
4
Update the information about Quantity by adding the new values from the
Transaction data set.
Resulting Log
This program has correctly updated one observation but it stopped when it could not find
a match for PartNumber value 6. The following lines are written to the SAS log:
Error Checking When Using Indexes to Randomly Access or Update Data 509
ERROR: No matching observation was found in Master data set.
PartNumber=6 AddQuantity=16 Quantity=70 _ERROR_=1
_IORC_=1230015 _N_=2
NOTE: The SAS System stopped processing this step because
of errors.
NOTE: The data set WORK.MASTER has been updated. There were
1 observations rewritten, 0 observations added and 0
observations deleted.
Resulting Data Set
The Master file was incorrectly updated. The updated master has five observations. One
observation was updated correctly, a new one was not added, and a second update was
not made. The following shows the incorrectly updated Master data set:
Master
OBS PartNumber Quantity
1 1 10
2 2 20
3 3 30
4 4 54
5 5 50
Revised Program
The objective is to apply two updates and one addition to Master. This action prevents
the DATA step from stopping when it does not find a match in Master for the
PartNumber value 6 in Transaction. By adding error checking, this DATA step is allowed
to complete normally and produce a correctly revised version of Master. This program
uses the _IORC_ automatic variable and the SYSRC autocall macro in a SELECT group
to check the value of the _IORC_ variable. If a match is found, the program executes the
appropriate code.
data master; 1
set transaction; 2
modify master key=PartNumber; 3
select(_iorc_); 4
when(%sysrc(_sok)) do;
Quantity = Quantity + AddQuantity;
replace;
end;
when(%sysrc(_dsenom)) do;
Quantity = AddQuantity;
_error_ = 0;
output;
end;
otherwise do;
put 'ERROR: Unexpected value for _IORC_= ' _iorc_;
put 'Program terminating. DATA step iteration # ' _n_;
put _all_;
stop;
end;
end;
run;
1
Open the Master data set for update.
510 Chapter 21 • Reading, Combining, and Modifying SAS Data Sets
2
Read an observation from the Transaction data set.
3
Match observations from the Master data set based on the value of PartNumber.
4
Take the correct course of action based on whether a matching value for PartNumber
is found in Master. Update Quantity by adding the new values from Transaction. The
SELECT group directs execution to the correct code. When a match occurs (_SOK),
update Quantity and replace the original observation in Master. When there is no
match (_DSENOM), set Quantity equal to the AddQuantity amount from
Transaction, and append a new observation. _ERROR_ is reset to 0 to prevent an
error condition that would write the contents of the program data vector to the SAS
log. When an unexpected condition occurs, write messages and the contents of the
program data vector to the log, and stop the DATA step.
Resulting Log
The DATA step executed without error and observations were appropriately updated and
added. The following lines are written to the SAS log:
NOTE: The data set WORK.MASTER has been updated. There were
2 observations rewritten, 1 observations added and 0
observations deleted.
Correctly Updated Master Data Set
Master contains updated quantities for PartNumber values 2 and 4 and a new observation
for PartNumber value 6. The following shows the correctly updated Master data set:
Master
OBS PartNumber Quantity
1 1 10
2 2 32
3 3 30
4 4 54
5 5 50
6 6 16
Example 2: Using Error Checking on All Statements That Use KEY=
Overview
This example shows how important it is to use error checking on all statements that use
the KEY= option when reading data.
Input Data Sets
The Master and Description data sets are both indexed on PartNumber. The Order data
set contains values for all parts in a single order. Only Order contains the PartNumber
value 8. The following shows the Master, Order, and Description input data sets:
Master ORDER
OBS PartNumber Quantity OBS PartNumber
1 1 10 1 2
2 2 20 2 4
3 3 30 3 1
Error Checking When Using Indexes to Randomly Access or Update Data 511
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.