Search the site...

SASCRUNCH TRAINING
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
  • Home
  • Member's Area
  • How to Start
  • SAS Interface
  • Creating a Data Set
  • Practical SAS Training Course
  • SAS Certified Specialist Training Program
  • Proc SQL Course
  • Introduction to Time Series Analysis
  • SAS Project Training Course
  • Full Training / Membership
  • Sign up
  • About us
  • Contact us
Sentry Page Protection
Please Wait...
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.
Picture
​​
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:
Picture

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

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.
Picture

When running the program above, you'll see the following error message on the LOG window:
Picture
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.
Picture

​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. ​
Picture

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

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.
Next

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.
Already a member? Go to member's area.