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 Certadv.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 certadv.frequentflyers;
quit;
Output 4.21 PROC SQL Query Result
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 Earned:',
sum(PointsEarned) format=comma12.
from certadv.frequentflyers union
select 'Total Points Traveled:',
sum(MilesTraveled) format=comma12.
from certadv.frequentflyers union
select 'Total Points Used:',
sum(PointsUsed) format=comma12.
from certadv.frequentflyers
;
quit;
Each SELECT clause defines
two columns: a character constant as a label and the summarized value.
The output is shown below.
Output 4.22 PROC SQL Query Result: Using a UNION Operator and Summary Functions