title 'Total Bookings by Vendor';
run;
The following output displays the results.
Figure 13.5 Putting Totals for Each BY Group in a New Data Set
Writing to Separate Data Sets
Writing Observations to Separate Data Sets
Tradewinds Travel wants overall information about the tours that were conducted this
year. One SAS data set is needed to contain detailed information about each tour,
including the total amount that was spent on that tour. Another SAS data set is needed to
contain the total number of bookings with each vendor and the total amount spent with
that vendor. Both of these data sets can be created using the techniques that you have
learned so far.
Begin the program by creating two SAS data sets from the SAS data set
MYLIB.SORTTOUR using the following DATA and SET statements:
data tourdetails vendordetails;
set mylib.sorttour;
The data set TOURDETAILS will contain the individual records, and
VENDORDETAILS will contain the information about vendors. The observations do
not need to be grouped for TOURDETAILS, but they need to be grouped by Vendor for
VENDORDETAILS.
If the data is not already grouped by Vendor, first use the SORT procedure. Add a BY
statement to the DATA step for use with VENDORDETAILS.
proc sort data=mylib.tourrevenue out=mylib.sorttour;
by Vendor;
run;
data tourdetails vendordetails;
set mylib.sorttour;
by Vendor;
run;
The only calculation that is needed for the individual tours is the amount of money that
was spent on each tour. Therefore, calculate the amount in an assignment statement and
write the observation to TOURDETAILS.
206 Chapter 13 Using More Than One Observation in a Calculation
Money=LandCost * NumberOfBookings;
output tourdetails;
The portion of the DATA step that builds TOURDETAILS is now complete.
Writing Totals to Separate Data Sets
Because observations remain in the program data vector after an OUTPUT statement
executes, you can continue using the observations in programming statements. The rest
of the DATA step creates information for the VENDORDETAILS data set.
Use the FIRST.Vendor variable to determine when SAS is processing the first
observation in each group.
Then set the Sum variables VendorBookings and VendorMoney to 0 in that observation.
VendorBookings accumulates the total number the bookings for each vendor, and
VendorMoney accumulates the total costs. Add the following statements to the DATA
step:
if First.Vendor then
do;
VendorBookings=0;
VendorMoney=0;
end;
VendorBookings + NumberOfBookings;
VendorMoney + Money;
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
about DO groups, see “Performing More Than One Action in an IF-THEN
Statement” on page 217.
The last observation in each BY–group contains the totals for that vendor. Therefore, use
the following statement to output the last observation to the data set
VENDORDETAILS:
if Last.Vendor then output vendordetails;
As a final step, use KEEP= and DROP= data set options to remove extraneous variables
from the two data sets so that each data set has just the variables that are wanted.
data tourdetails(drop=VendorBookings VendorMoney)
vendordetails(keep=Vendor VendorBookings VendorMoney);
The Program
The following is the complete program that creates the VENDORDETAILS and
TOURDETAILS data sets:
proc sort data=mylib.tourrevenue out=mylib.sorttour;
by Vendor;
run;
data tourdetails(drop=VendorBookings VendorMoney)
vendordetails(keep=Vendor VendorBookings VendorMoney);
set mylib.sorttour;
by Vendor;
Money=LandCost * NumberOfBookings;
output tourdetails;
Writing to Separate Data Sets 207
if First.Vendor then
do;
VendorBookings=0;
VendorMoney=0;
end;
VendorBookings + NumberOfBookings;
VendorMoney + Money;
if Last.Vendor then output vendordetails;
run;
proc print data=tourdetails;
title 'Detail Records: Dollars Spent on Individual Tours';
run;
proc print data=vendordetails;
title 'Vendor Totals: Dollars Spent and Bookings by Vendor';
run;
The following output displays detail tour records in one SAS data set and vendor totals
in another.
Figure 13.6 Detail Tour Records in the TOURDETAILS Data Set
208 Chapter 13 Using More Than One Observation in a Calculation

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

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