Sample 24678: Update a data set in place using MODIFY statement and matching access with a BY statement
 |  |  |  |  |  |
Illustrate how updating a data set in place using MODIFY
with a BY statement works with and without duplicates in
the Master and Transaction data sets.
Note: This technique matches the value(s) of one or more BY variables in the MASTER data set against the same variables in the second or TRANSACTION data set.
MASTER is the data set you want to modify and must be named on the DATA statement. A BY statement is required. The data sets are not required to be in sorted order, but it does greatly reduce I/O.
Like-named variables in TRANSACTION will overlay values
in MASTER.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
/*****************************************************************************/
/* If your master data set and transaction data set both contain unique */
/* values for the BY variables, see Example 1 below. */
/* */
/* If your master data set contains duplicate values, only the first */
/* observation in BY group is updated. The BY statement with MODIFY */
/* generates a WHERE clause that returns only the first occurence of the BY */
/* variable(s) from the master data set. See Example 2 below. */
/* */
/* If your transaction data set contains duplicates, updates are applied one */
/* after the other to the matching observation in the master data set. */
/* See Example 3a below. If you are generating a new total using all the */
/* duplicate values from the transaction data set, be sure to use an */
/* accumulative statement. See Example 3b below. */
/* */
/* Due to the implicit REPLACE in a MODIFY, if there is an observation in */
/* the TRANSACTION data set that is not in the MASTER data set, you need to */
/* reset the _ERROR_ flag so the step can continue processing. */
/*****************************************************************************/
/* Example 1: MASTER and TRANSACTION data sets have unique values */
/* Create STOCK data set */
data stock;
input partno $ desc $ instock price;
datalines;
K89R seal 34 245.00
M4J7 sander 98 45.88
LK43 filter 121 10.99
MN21 brace 43 27.87
;
data trans;
input partno $ desc $ instock price;
datalines;
K89R seal 34 300
M123 sander 98 45.88
;
/* NOTE: PARTNO value M123 (from TRANS) does not exist in the MASTER data set. */
/* This triggers an error condition that will return an _IORC_ value of 1230013 */
/* and set _ERROR_ to 1. To keep the step running, we can check for this */
/* return code, and reset the _ERROR_ flag or simply check for a match */
/* condition, otherwise reset the _ERROR_ flag. */
/* */
/* The value of _IORC_ can be checked using its numeric value or with */
/* the autocall macro %SYSRC mnemonic. Example 2 uses %SYSRC. */
/* */
/* _DSENMR=1230013 TRANSACTION data set observation does not exist in MASTER */
/* _DSEMTR=1230014 Multiple TRANSACTION data set observation does not exist */
/* on MASTER */
/* _SOK =0 The I/O operation was successful */
data stock;
modify stock trans;
by partno;
/* If a match is found, replace */
if _IORC_=0 then replace;
/* Otherwise, reset the error flag, implied replace */
/* is overwritten due to logic above, observation is not added */
else _ERROR_=0;
run;
proc print data=stock;
run;
/* Example 2: Master contains duplicates for PARTNO */
/* WORK.TRANS remains the same. */
data stock_dup;
input partno $ desc $ instock price;
datalines;
K89R seal 34 245.00
K89R seal 33 240.00
K89R seal 30 235.00
M4J7 sander 98 45.88
LK43 filter 121 10.99
MN21 brace 43 27.87
;
data stock_dup;
modify stock_dup trans;
by partno;
if _IORC_=%sysrc(_SOK) then replace;
else if _IORC_=%sysrc(_DSENMR) then _ERROR_=0;
else put 'Unexpected I/O error occurred';
run;
/* Notice that only the first observation for PARTNO="K89R" is modified */
proc print data =stock_dup;
run;
/* Example 3a: Duplicates in WORK.TRANS only */
/* WORK.STOCK remains the same. */
data trans_dup;
input partno $ desc $ instock price;
datalines;
K89R seal 34 300
K89R seal 04 300
K89R seal 10 300
M123 sander 98 45.88
;
data stock;
modify stock trans_dup;
by partno;
if _IORC_=0 then replace;
else _ERROR_=0;
run;
/* Note the value of STOCK. All duplicates for PARTNO are */
/* applied consecutively to the matching observation in STOCK. */
/* The last one applied is the final value. */
proc print data=stock;
run;
/* Example 3b: Accumulating duplicates from TRANS for INSTOCK */
/* Use original version of STOCK and TRANS_DUP. */
/* Rename STOCK from TRANS, when there is a match, sum current */
/* value of INSTOCK with current value of STOCK to get running */
/* total for all transactions. */
data stock;
modify stock trans_dup(rename=(instock=stock));
by partno;
if _IORC_=0 then do;
instock + stock;
replace;
end;
else _ERROR_=0;
run;
proc print data=stock;
run;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
Example 1: MASTER and TRANSACTION data sets have unique values Obs PARTNO DESC INSTOCK PRICE 1 K89R seal 34 300.00 2 M4J7 sander 98 45.88 3 LK43 filter 121 10.99 4 MN21 brace 43 27.87 Example 2: Master contains duplicates for PARTNO Obs PARTNO DESC INSTOCK PRICE 1 K89R seal 34 300.00 2 K89R seal 33 240.00 3 K89R seal 30 235.00 4 M4J7 sander 98 45.88 5 LK43 filter 121 10.99 6 MN21 brace 43 27.87 Example 3a: Duplicates in WORK.TRANS only Obs PARTNO DESC INSTOCK PRICE 1 K89R seal 10 300.00 2 M4J7 sander 98 45.88 3 LK43 filter 121 10.99 4 MN21 brace 43 27.87 Example 3b: Accumulating duplicates from TRANS for INSTOCK Obs PARTNO DESC INSTOCK PRICE 1 K89R seal 82 300.00 2 M4J7 sander 98 45.88 3 LK43 filter 121 10.99 4 MN21 brace 43 27.87
Illustrate how updating a data set in place using MODIFY
with a BY statement works with and without duplicates in
the Master and Transaction data sets.
| Type: | Sample |
| Topic: | SAS Reference ==> DATA Step SAS Reference ==> Statements ==> File-handling ==> MODIFY SAS Reference ==> Statements ==> File-handling ==> MODIFY ==> with BY |
| Date Modified: | 2008-01-25 14:36:18 |
| Date Created: | 2004-09-30 14:09:03 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |