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.
Note: As an alternative to using
the keyword CALCULATED, repeat the calculation in the WHERE clause.
However, this method is inefficient because PROC SQL has to perform
the calculation twice. In the preceding query, the alternate WHERE
statement would be:
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.
Note: The CALCULATED keyword is
a SAS enhancement and is not specified in the ANSI Standard for SQL.