Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

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

Operating System and Release Information