You should already know how to define a new column by using the SELECT clause and performing a calculation. For example, the following PROC SQL query creates the new column Total by adding the values of three existing columns: Boarded, Transferred, and Nonrevenue:

proc sql outobs=10;
   select flightnumber, date, destination,
          boarded + transferred + nonrevenue
          as Total
      from sasuser.marchflights

You can also use a calculated column in the WHERE clause to subset rows. However, because of how SQL queries are processed, you cannot just specify the column alias in the WHERE clause. To see what happens, we take the preceding PROC SQL query and add a WHERE clause in the SELECT statement to reference the calculated column Total, as shown below:

proc sql outobs=10;
   select flightnumber, date, destination,
          boarded + transferred + nonrevenue
          as Total
      from sasuser.marchflights
      where total < 100;

When this query is executed, the following error message is displayed in the SAS log.

519  proc sql outobs=10;
520      select flightnumber, date, destination,
521             boarded + transferred + nonrevenue
522             as Total
523          from sasuser.marchflights
524          where total < 100;
ERROR: The following columns were not found in the contributing tables: total.

This error message is generated because, in SQL queries, the WHERE clause is processed before the SELECT clause. The SQL processor looks in the table for each column named in the WHERE clause. The table Sasuser.Marchflights does not contain a column named Total, so SAS generates an error message.

When you use a column alias in the WHERE clause to refer to a calculated value, you must use the keyword CALCULATED along with the alias. The CALCULATED keyword informs PROC SQL that the value is calculated within the query. Now, the PROC SQL query looks like this:

 proc sql outobs=10;
    select flightnumber, date, destination,
           boarded + transferred + nonrevenue
           as Total
       from sasuser.marchflights
       where calculated total < 100;

This query executes successfully and produces the following output.

where boarded + transferred + nonrevenue <100;

You can also use the CALCULATED keyword in other parts of a query. In the following example, the SELECT clause calculates the new column Total and then calculates a second new column based on Total. To create the second calculated column, you have to specify the keyword CALCULATED in the SELECT clause.

proc sql outobs=10;
   select flightnumber, date, destination,
          boarded + transferred + nonrevenue
          as Total,
          calculated total/2 as Half
       from sasuser.marchflights;

This query produces the following output.