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

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