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

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