Figure 13.7 Vendor Totals in the VENDORDETAILS Data Set
Using a Value in a Later Observation
A further requirement of Tradewinds Travel is a separate SAS data set that contains the
tour that generated the most revenue. (The revenue total equals the price of the tour
multiplied by the number of bookings.) One method of creating the new data set might
be to follow these three steps:
1. Calculate the revenue in a DATA step.
2. Sort the data set in descending order by the revenue.
3. Use another DATA step with the OBS= data set option to write that observation.
A more efficient method compares the revenue from all observations in a single DATA
step. SAS can retain a value from the current observation to use in future observations.
When the processing of the DATA step reaches the next observation, the held value
represents information from the previous observation.
The RETAIN statement causes a variable that is created in the DATA step to retain its
value from the current observation into the next observation. The variable is not set to
missing at the beginning of each iteration of the DATA step. RETAIN is a declarative
statement, not an executable statement. This statement has the following form:
RETAIN variable-1 < . . . variable-n>;
To compare the Revenue value in one observation to the Revenue value in the next
observation, create a retained variable named HoldRevenue and assign the value of the
current Revenue variable to it. In the next observation, the HoldRevenue variable
contains the Revenue value from the previous observation, and its value can be
compared to that of Revenue in the current observation.
To see how the RETAIN statement works, look at the next example. The following
DATA step writes observations to data set TEMP before SAS assigns the current revenue
to HoldRevenue:
data temp;
set mylib.tourrevenue;
retain HoldRevenue;
Revenue=LandCost * NumberOfBookings;
output;
HoldRevenue=Revenue;
run;
Using a Value in a Later Observation 209
proc print data=temp;
var Country LandCost NumberOfBookings Revenue HoldRevenue;
title 'Tour Revenue';
run;
The following output displays the results.
Figure 13.8 Retaining a Value By Using the Retain Statement
The value of HoldRevenue is missing at the beginning of the first observation. It is still
missing when the OUTPUT statement writes the first observation to TEMP. After the
OUTPUT statement, an assignment statement assigns the value of Revenue to
HoldRevenue. Because HoldRevenue is retained, that value is present at the beginning
of the next iteration of the DATA step. When the OUTPUT statement executes again, the
value of HoldRevenue still contains that value.
To find the largest value of Revenue, assign the value of Revenue to HoldRevenue only
when Revenue is larger than HoldRevenue, as shown in the following program:
data mostrevenue;
set mylib.tourrevenue;
retain HoldRevenue;
Revenue=LandCost * NumberOfBookings;
if Revenue > HoldRevenue then HoldRevenue=Revenue;
run;
proc print data=mostrevenue;
var Country LandCost NumberOfBookings Revenue HoldRevenue;
title 'Tour Revenue';
run;
210 Chapter 13 Using More Than One Observation in a Calculation
The following output displays the results.
Figure 13.9 Holding the Largest Value in a Retained Variable
The value of HoldRevenue in the last observation represents the largest revenue that is
generated by any tour. To determine which observation the value came from, create a
variable named HoldCountry to hold the name of the country from the observation with
the largest revenue. Include HoldCountry in the RETAIN statement to retain its value
until explicitly changed. Then use the END= data set option to select the last
observation, and use the KEEP= data set option to keep only HoldRevenue and
HoldCountry in MOSTREVENUE:
data mostrevenue (keep=HoldCountry HoldRevenue);
set mylib.tourrevenue end=LastOne;
retain HoldRevenue HoldCountry;
Revenue=LandCost * NumberOfBookings;
if Revenue > HoldRevenue then
do;
HoldRevenue=Revenue;
HoldCountry=Country;
end;
if LastOne;
run;
proc print data=mostrevenue;
title 'Country with the Largest Value of Revenue';
run;
Note: The program uses a DO group. Using DO groups enables the program to evaluate
a condition once and take more than one action as a result. For more information
Using a Value in a Later Observation 211

Get Step-by-Step Programming with Base SAS 9.4, Second Edition, 2nd Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.