We can demonstrate another
realistic business problem, to see how summary functions can be used
with a set operator (in this case, UNION). Suppose you want to display
the following summarized data for members of a frequent-flyer program:
total points earned, total points used, and total miles traveled.
All three values can be calculated from columns in the table Sasuser.Frequentflyers
by using summary functions.
You might wonder why
set operations are needed when only one table is involved. If you
wanted to display the three summarized values horizontally, in three
separate columns, you could solve the problem without a set operation,
using the following simple SELECT statement:
proc sql;
select sum(pointsearned) format=comma12.
label='Total Points Earned',
sum(pointsused) format=comma12.
label='Total Points Used',
sum(milestraveled) format=comma12.
label='Total Miles Traveled'
from sasuser.frequentflyers;
Assume, however, that
you want the three values to be displayed vertically in a single column.
To generate this output, you create three different queries on the
same table, and then use two UNION set operators to combine the three
query results:
proc sql;
title 'Points and Miles Traveled';
title2 'by Frequent Flyers';
select 'Total Points Traveled:',
sum(MilesTraveled) format=comma12.
from sasuser.frequentflyers
union
select 'Total Points Earned:',
sum(PointsEarned) format=comma12.
from sasuser.frequentflyers
union
select 'Total Points Used:',
sum(PointsUsed) format=comma12.
from sasuser.frequentflyers;
Each SELECT clause defines
two columns: a character constant as a label and the summarized value.
The output is shown below.
Note: The preceding program reads
the same table three times, so it is not the most efficient way to
solve this problem.