SAS Certification Prep Guide, 4th Edition

Subsetting Rows By Using Calculated Values

Understanding How PROC SQL Processes Calculated Columns

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 ...