Using More Than One Observation in a Calculation

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 money that was spent on that tour. Another SAS data set is needed to contain the total number of schedulings with each vendor and the total money 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 are 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 record to TOURDETAILS.

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 them 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 totals the schedulings for each vendor, and VendorMoney totals the 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 on DO groups, see Performing More Than One Action in an IF-THEN Statement. [cautionend]

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:

options pagesize=60 linesize=80 pageno=1 nodate;
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;
   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 the results:

Detail Tour Records in One SAS Data Set and Vendor Totals in Another

               Detail Records: Dollars Spent on Individual Tours               1

                                                     Number
                                Land                   Of
          Obs    Country        Cost    Vendor      Bookings    Money

            1    France          575    Express        10        5750
            2    India           489    Express         .           .
            3    Japan           720    Express        10        7200
            4    Greece          698    Express        20       13960
            5    Italy           468    Express         9        4212
            6    Ireland         558    Express         9        5022
            7    New Zealand    1489    Southsea        6        8934
            8    Australia      1079    Southsea       10       10790
            9    Spain           510    World          12        6120
           10    Brazil          540    World           6        3240
           11    Venezuela       425    World           8        3400
           12    USSR            924    World           6        5544
           13    Switzerland     734    World          20       14680

              Vendor Totals: Dollars Spent and Bookings by Vendor              2

                                         Vendor     Vendor
                     Obs    Vendor      Bookings     Money

                      1     Express        58        36144
                      2     Southsea       16        19724
                      3     World          52        32984

Top of Page