Sometimes, you might want to specify an in-line view rather than a table as the source of data for a PROC SQL query. An in-line view is a nested query that is specified in the outer query's FROM clause. (You should already be familiar with a subquery, which is a nested query that is specified in a WHERE clause.) An in-line view selects data from one or more tables in order to produce a temporary (or virtual) table that the outer query then uses to select data for output.

For example, the following FROM clause specifies an in-line view:

 from (select flightnumber, date,
              boarded/passengercapacity*100 
              as pctfull
              format=4.1 label='Percent Full'
         from sasuser.marchflights)

This in-line view selects two existing columns (FlightNumber and Date) and defines the new column PctFull based on the table Sasuser.Marchflights.

Unlike a table, an in-line view exists only during query execution. Because it is temporary, an in-line view can be referenced only in the query in which it is defined. In addition, an in-line view can be assigned an alias but it cannot be assigned a permanent name.

There are two potential advantages to using an in-line view instead of a table in a PROC SQL query:

The preceding FROM clause is from a simple PROC SQL query that references just one data source: the in-line view. However, a PROC SQL query can join multiple tables and in-line views. For example, the FROM clause shown below specifies both a table (Sasuser.Flightschedule) and an in-line view.

 from sasuser.flightschedule,
      (select flightnumber, date,
              boarded/passengercapacity*100 
              as pctfull
              format=4.1 label='Percent Full'
         from sasuser.marchflights)

You can specify more than one table in the FROM clause of an in-line view, as shown in the following example:

  from (select marchflights.flightnumber, 
               marchflights.date,
               boarded/passengercapacity*100 
               as pctfull
               format=4.1 label='Percent Full',
               delay
           from sasuser.marchflights, 
                sasuser.flightdelays
           where marchflights.flightnumber=
                 flightdelays.flightnumber
                 and marchflights.date=
                 flightdelays.date)

In other words, you can base an in-line view on a join.

You can assign an alias to an in-line view just as you can to a table. In the following example, the alias f has been added in the first FROM clause to reference the table Sasuser.Flightschedule and the alias m is associated with the results from the in-line view. After the main FROM clause, a WHERE clause that uses both of the aliases has been added.

from sasuser.flightschedule as f,
     (select flightnumber, date
             boarded/passengercapacity*100 
             as pctfull
             format=4.1 label='Percent Full'
         from sasuser.marchflights) as m
where m.flightnumber=f.flightnumber
      and m.date=f.date

Suppose you want to identify the air travel destinations that experienced the worst delays in March. You would like your output to show all of the following data:

Your PROC SQL query uses an in-line view to calculate all of the new columns except for the last one:

proc sql;
title "Flight Destinations and Delays";
   select destination, 
          average format=3.0 label='Average Delay',
          max format=3.0 label='Maximum Delay', 
          late/(late+early) as prob format=5.2
          label='Probability of Delay'
      from (select destination,
                   avg(delay) as average,
                   max(delay) as max,
                   sum(delay > 0) as late,
                   sum(delay <= 0) as early
                   from sasuser.flightdelays
                   group by destination)
      order by average;

Consider each clause of the outer query, starting with the FROM clause, because PROC SQL evaluates the FROM clause before the SELECT clause.

The FROM clause specifies an in-line view rather than a table. The in-line view (nested query) specifies the following columns that are in the table Sasuser.Flightdelays or are based on a column in that table:

The columns Average, Max, Late, and Early are all calculated by using summary functions.

In the calculation for the columns Late and Early, a Boolean expression is used as the argument for the summary function. A Boolean function resolves either to 1 (true) or 0 (false). For example, Late is calculated by taking the sum of the Boolean expression delay > 0. For every value of Delay that is greater than 0, the Boolean expression resolves to 1; values that are equal to or less than 0 resolve to 0. The SUM function adds all values of Delay to indicate the number of delays that occurred for each destination.

The in-line view concludes with the clause group by destination, specifying that the in-line view data should be grouped, and summarized by the values of Destination.

If you submitted this in-line view (nested query) as a separate query, it would generate the following output.

Consider the outer query's SELECT and ORDER BY clauses:

proc sql;
title "Flight Destinations and Delays";
   select destination,
          average format=3.0 label='Average Delay',
          max format=3.0 label='Maximum Delay',
          late/(late+early) as prob format=5.2
          label='Probability of Delay'
      from (select destination,
                   avg(delay) as average,
                   max(delay) as max,
                   sum(delay > 0) as late,
                   sum(delay <= 0) as early
                   from sasuser.flightdelays
                   group by destination)
      order by average;
 

The outer query's SELECT clause specifies columns as follows:

The outer query's last clause is an ORDER BY clause. The output is sorted by the values of Average.

This PROC SQL query generates the following output.

Later in this chapter, a PROC SQL query that combines multiple tables and uses an in-line view is explained.