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 II [3-17]


Table Alias
A table alias is a temporary name that can be used in place of the actual table name in a Proc SQL query.

Let's look at an example.

Copy and run the code from the yellow box below:

The PRODUCT table contains a list of items and their associated prices:
Picture

The INVENTORY table contains the same list of items and their stock status.
Picture

We are going to join the two tables by matching the ITEM column.
Proc sql;
select *
from product, inventory
where product.item = inventory.item;
quit;

Since the matching column (i.e. ITEM) has the same name in the two input tables, the column is prefixed with the table names (e.g. product.item).

Another way to prefix the column is to use what we called a table alias.

Example
Proc sql;
select *
from product as p, inventory as i
where p.item = i.item;
quit;

The table aliases are defined in the FROM clause. 
Picture

In our example, p and i are the aliases created for the PRODUCT and INVENTORY tables. 

They can be used in place of the actual table name in the WHERE clause:
Picture

Notes:
  1. Using a table alias is usually optional. It helps making the code easier to understand.
  2. The keyword "as" is also optional. Defining the alias without it will work just fine. Below is an example.
Proc sql;
select *
from product p, inventory i
where p.item = i.item;
quit;

The aliases in this Proc SQL step are defined without the "as" keyword.

It will still give the same results.
Picture

Exercise

Copy and run the code from the yellow box below:

The AMAZON and LOCAL tables each contain a list of tea prices.

Write a Proc SQL step to inner join the two tables by matching the teas from the two tables. Also do the following:
  1. Rename the PRICE column from the AMAZON table as AMAZONPRICE.
  2. Rename the PRICE column from the LOCAL table as LOCALPRICE.
  3. Create a new column that contains the lower price of the two prices. Name the column as LOWERPRICE.
Next

Need some help? 


HINT:
Use the MIN function to compute the lower of the two prices.


SOLUTION:

Proc sql;
select a.teas,
a.price as amazonprice,
l.price as localprice,
min(a.price,l.price) as lowerprice
from amazon as a,local as l
where a.teas = l.teas;
quit;


Fill out my online form.
Already a member? Go to member's area.