The first few paragraphs below are mostly general comments about column aliasing in the SQL language. The remaining paragraphs are specific to PROC SQL. Effectively, a column alias can be considered to be a temporary renaming of the expression that is being referenced. The duration of this renaming lasts for the lifetime of the query's execution. For example, in the code below, the qty column has been renamed to Quantity and the expression cost+100 has been renamed to ListPrice. title 'columns with aliases'; select qty as Quantity, cost, cost+100 as ListPrice from calc; The template for an SQL query can be specified, at a high abstraction level, as follows below. The parenthesized numbers at the right of each query part represents its position in the (conceptual) execution order of the parts of an SQL query. More will be said about this below. Please note that planners/optimizers, such as the one that is present in PROC SQL, have the freedom to alter internal execution orders (as long as any alterations do NOT change the meaning of a/the query). select <select list> (5) from <from clause> (1) where <where clause> (2) group by <group by clause> (3) having <having clause> (4) order by <order by clause> (6) Of course, the only mandatory parts of an SQL query are the SELECT and FROM, The other four parts may be optional, depending on what someone is trying to accomplish with the query. Technically, the ORDER BY part is not part of the query. Rather, it is part of a cursor. However, for simplicity, we can pretend that it is part of the query. From the ISO/ANSI SQL Standards perspective, the conceptual order of execution of the six parts, if all are present, is this sequence: the FROM part is executed first, the WHERE part is executed second, the GROUP BY part is executed third, the HAVING part is executed fourth, the SELECT part is executed fifth, and the ORDER BY part is executed last. The ISO/ANSI SQL Standards do not require that the value that is associated with any column alias be present until ORDER BY-time. More specifically, there is no guarantee that a column alias will work as expected if it appears on the clauses that are parts of execution steps 2, 3, and 4, namely, the WHERE clause, GROUP BY clause, and HAVING clause. A column alias is not guaranteed to have been resolved until the beginning of execution step 6 (the start of execution for the ORDER BY part). The major ramificaton of this is that, per the ISO/ANSI SQL Standards rules regarding column aliases, there is no guarantee, whatsoever, that a SQL processor will resolve a column alias in time for it to be referenced in a <where clause>, <group by clause>, or <having clause>. However, per the Standards, any column alias must be available for reference by the time that the <order by clause> is processed. Hence, for maximum portability of SQL code, it is best to AVOID writing code that refers to column aliases in either a <where clause>, <group by clause>, or <having clause>. In PROC SQL, if a column alias is just a rename of a table column, then either the table name or column name can be used in the <where clause>, <having clause>, or <order by clause>. For example, the first alias in each SELECT part is just a rename of a table column whereas the second alias refers to a calculated epression. Both the first and second SQL statements below will output the expected results in PROC SQL. /*-- preferred since no column alias is referenced in the <where clause> --*/ /*-- PORTABLE to other SQL processors --*/ select qty as Quantity, cost, cost+100 as ListPrice from calc where qty > 5; /*-- this will work in PROC SQL, but may not work with some other SQL processors --*/ select qty as Quantity, cost, cost+100 as ListPrice from calc where Quantity > 5; The development of what we know today as PROC SQL began in the 1984-85 timeframe. Some of its scoping and aliasing rules predate those of the first ANSI SQL Standard (1986) and the first ISO SQL Standard (1987). One of the early extensions that was made to PROC SQL was the development of the CALCULATED keyword. Basically, this keyword enables PROC SQL users to reference column aliases that are associated with calculated expressions. The column alias that is being referenced by the CALCULATED keyword can be on either the <where clause>, <group by clause>, <having clause>, or <order by clause>. Make note, though, that the use of the CALCULATED keyword is redundant if is used on the <order by clause> to refer to a column alias because that alias will have been resolved by the time that the execution of the ORDER BY part of a query begins. Now, for an example that uses the CALCULATED keyword. Suppose we wish to subset the rows by the value that is associated with the second alias (i.e., ListPrice). Then, it can be accomplished in PROC SQL by this code. /*-- note the use of the CALCULATED keyword --*/ select qty as Quantity, cost, cost+100 as ListPrice from calc where CALCULATED ListPrice > 1500; Unfortunately, the above code is not portable. The ISO/ANSI SQL Standards-approved way of accomplishing this is as follows. /*-- note that the ListPrice column alias does not appear in the <where clause> --*/ select qty as Quantity, cost, cost+100 as ListPrice from calc where cost+100 > 1500; I hope that the above comments help to clarify matters somewhat.
... View more
