Sentry Page Protection
Proc SQL [6-14]
WHERE Clause with Calculated Column
WHERE calculated
comm > 2000;
comm > 2000;
When referencing a new column (as opposed to an existing column) in the WHERE clause, the CALCULATED keyword must be used.
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.
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.
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;
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;
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;
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:
Subset the output, and display only the cars where the difference between the MSRP and INVOICE is at least $9,000.
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?
Fill out my online form.