Reusing calculated columns in Netezza and SAS queries

Netezza and SAS allow a query to reference a calculated column by name in the SELECT, WHERE, and ORDER BY clauses. Based on the DRY principle, this reduces code and makes code easier to read and maintain.

Some people call calculated columns derived or computed columns.

In Microsoft SQL Server, SQLite, and other RDBMSs you cannot exactly do this: a workaround is to reference a subquery or view. In Microsoft SQL Server, you can also define a computed column on a table.

Below is an example tested with Netezza 7.2. Notice height_m is used in the SELECT clause, and bmi is used in the WHERE and ORDER BY clauses.

CREATE TEMP TABLE people (weight_kg INT, height_m float);

INSERT INTO people
VALUES (50, 1.6);

INSERT INTO people
VALUES (70, 1.8);

INSERT INTO people
VALUES (150, 1.8);

SELECT weight_kg
 ,height_m
 ,height_m*height_m as height_m_squared
 ,weight_kg/(height_m_squared)::int as bmi
FROM people
WHERE bmi < 30
ORDER BY bmi;

Below is an example tested with SAS 9.4.

data people;
  input weight_kg height_m;
datalines;
50 1.6
70 1.8
150 1.8
;

proc sql;
  select
    weight_kg,
    height_m,
    height_m*height_m as height_m_squared,
    weight_kg/(calculated height_m_squared) as bmi
  from
    people
  where
    calculated bmi < 30
  order by
     calculated bmi;
quit;