title2 "Supplemented when Available from Prior Year's League";
||' '||prioryr.lastname)as Name format=$26.,
coalesce(lastyr.avgscore,prioryr.avgscore)as Average format=8.
from league1 as lastyr full join league2 as prioryr
order by Bowler;
Output 6.6 PROC SQL Output for Overlaying Missing Values
How It Works
This solution uses a full join to obtain all rows from League1 as well as all rows from
League2. The program uses the COALESCE function on each column so that, whenever
possible, there is a value for each column of a row. Using the COALESCE function on a
list of expressions that is enclosed in parentheses returns the first nonmissing value that
is found. For each row, the following code returns the AvgScore column from League1
coalesce(lastyr.avgscore,prioryr.avgscore) as Average format=8.
If this value of AvgScore is missing, then COALESCE returns the AvgScore column
from League2 for Average. If this value of AvgScore is missing, then COALESCE
returns a missing value for Average.
In the case of the Name column, the COALESCE function returns the value of FullName
from League1 if it exists. If not, then the value is obtained from League2 by using both
the TRIM function and concatenation operators to combine the first name and last name
Finally, the table is ordered by Bowler. The Bowler column is the result of the
Because the value is obtained from either table, you cannot confidently order the output
by either the value of Bowler in League1 or the value of AMFNo in League 2, but only
by the value that results from the COALESCE function.
184 Chapter 6 • Practical Problem-Solving with PROC SQL