O'Reilly logo

Step-by-Step Programming with Base SAS 9.4, Second Edition, 2nd Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Understanding How Duplicate BY Variables Affect
File Update
How the DATA Step Processes Duplicate BY Variables
When you use a BY statement with MODIFY, both the master and the transaction data
sets can have observations with duplicate values of BY variables. Neither the master nor
the transaction data set needs to be sorted, because BY-group processing uses dynamic
WHERE processing to find an observation in the master data set.
The DATA step processes duplicate observations in the following ways:
If duplicate BY values exist in the master data set, then MODIFY applies the current
transaction to the first occurrence in the master data set.
If duplicate BY values exist in the transaction data set, then the observations are
applied one on top of another so that the values overwrite each other. The value in
the last transaction is the final value in the master data set.
If both the master and the transaction data sets contain duplicate BY values, then
MODIFY applies each transaction to the first occurrence in the group in the master
data set.
The Program
The program in this section updates the INVENTORY_2 master data set with
observations from the ADD_INVENTORY_2 transaction data set. Both data sets contain
consecutive and nonconsecutive duplicate values of the BY variable PartNumber.
The following program creates the master data set INVENTORY_2. Note that the data
set contains three observations for PartNumber M4J7.
data inventory_2;
input PartNumber $ Description $ InStock @17
ReceivedDate date9. @27 Price;
format ReceivedDate date9.;
datalines;
K89R seal 34 27jul1998 245.00
M4J7 sander 98 20jun2012 45.88
M4J7 sander 98 20jun2012 45.88
LK43 filter 121 19may2013 10.99
MN21 brace 43 10aug2013 27.87
M4J7 sander 98 20jun2012 45.88
BC85 clamp 80 16aug2013 9.55
NCF3 valve 198 20mar2013 24.50
KJ66 cutter 6 18jun2013 19.77
;
run;
proc print data=inventory_2;
title 'INVENTORY_2 Data Set';
run;
Understanding How Duplicate BY Variables Affect File Update 343
The following output displays the INVENTORY_2 data set:
Figure 21.4 The INVENTORY_2 Data Set
The following program creates the transaction data set ADD_INVENTORY_2, and then
modifies the master data set INVENTORY_2. Note that the data set
ADD_INVENTORY_2 contains three observations for PartNumber M4J7.
data add_inventory_2;
input PartNumber $ Description $ NewStock;
datalines;
K89R abc 17
M4J7 def 72
M4J7 ghi 66
LK43 jkl 311
M4J7 mno 43
BC85 pqr 75
;
run;
data inventory_2;
modify inventory_2 add_inventory_2;
by PartNumber;
ReceivedDate=today();
InStock=InStock+NewStock;
run;
proc print data=inventory_2;
title 'Tool Warehouse Inventory';
run;
344 Chapter 21 Modifying SAS Data Sets

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required