NOTE: CALCULATED in SQL

If you use SQL, either hand-cranked from the Editor, or generated via Enterprise Guide or DI Studio, you're probably familiar with creating new columns ("computed columns"). However, have you ever struggled to create a computed column from a computed column?

You can do this easily in SQL, but the syntax isn't immediately obvious. For example, calculating body mass index could be attempted with a two-stage calculation:

18 /* Assume height measured in metres, weight measured in kilos, */
19 /* then BMI = weight / height^2 */
20 proc sql;
21   create table alpha as
22     select  class.*
23            ,height * height  as heightSquared
24            ,weight / heightSquared  as bmi
25     from sashelp.class;
ERROR: The following columns were not found in the contributing tables: heightSquared.

We can create a "heightSquared" computed column, but trying to use heightSquared to create bmi fails.

The solution is simple: just add the keyword "calculated" in front of the computed column when you want to refer to it. Here's the corrected select expression:

     select  class.*
            ,height * height  as heightSquared
            ,weight / CALCULATED heightSquared  as bmi

You can use the CALCULATED keyword in all places where you can use SQL in SAS.