Sentry Page Protection
Proc SQL [6-14]

WHERE Clause with Calculated Column
WHERE calculated
  comm > 2000;
​
When referencing a new column (as opposed to an existing column) in the WHERE clause, the CALCULATED keyword must be used.
​​
Let's take a look at an example.

Locate the CARS table from the SASHelp library.

The CARS table contains the cars from a list of car makers.
Picture
Partial output

Let's assume the commission for each car is 2% of the MSRP.

The commission can be computed as below:

Example
Proc sql;
select make, model, msrp,
  0.02 * msrp as comm
from sashelp.cars;
quit;

​The commission is calculated and displayed in the COMM column:

Note: the COMM column did not exist in the CARS table. It is a new column created in the SELECT clause.

Now, let's add a WHERE clause that references the COMM column in the Proc SQL step.

[Note: there will be an error message when running the code below.]

​Example
Proc sql;
select make, model, msrp,
  0.02 * msrp as comm
from sashelp.cars
where comm >2000;
quit;

The COMM column is referenced in the WHERE clause to select only the cars with commission greater than $2,000.

When running the program above, you'll see the following error message on the LOG window:
ERROR: The following columns were not found in the contributing tables: comm.

​There is an error because, in Proc SQL, SAS processes the WHERE clause before the SELECT clause, despite the fact that it is positioned after the SELECT clause.

​The COMM column was not created yet when the WHERE clause is processed. 

As a result, SAS generated an error message stating the COMM column was not found in the table.

In order to reference a calculated column in the WHERE clause, you must add the CALCULATED keyword before the column.
​
Example
Proc sql;
select make, model, msrp,
  0.02 * msrp as comm
from sashelp.cars
where calculated 
  comm >2000;
quit;

The CALCULATED keyword is added before the COMM column. ​

SAS understands it is a calculated column and it processes the step accordingly.​
Exercise

Locate the CARS table from the SASHelp library. 

Write a Proc SQL step to display the difference between MSRP and Invoice for each car.

The output table should display the following five columns:
  • MAKE
  • MODEL
  • MSRP
  • INVOICE
  • DIFF (calculated as MSRP - INVOICE)

Subset the output, and display only the cars where the difference between the MSRP and INVOICE is at least $9,000.
Need some help? 


HINT:

Use the CALCULATED keyword when referencing the DIFF column in the WHERE clause.


SOLUTION:

proc sql;
select make, model, msrp, invoice, msrp-invoice as diff
from sashelp.cars
where calculated diff > 9000;
quit;

or

proc sql;
select make, model, msrp, invoice, msrp-invoice as diff
from sashelp.cars
where msrp-invoice > 9000;
quit;

Fill out my online form.