| This post was kindly contributed by NOTE: The blog of RTSL.eu - Development with SAS® - go there to comment and to read the full post. |
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.
| This post was kindly contributed by NOTE: The blog of RTSL.eu - Development with SAS® - go there to comment and to read the full post. |