Sentry Page Protection
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:
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:
The INVENTORY table contains the same list of items and their stock status.
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;
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;
select *
from product as p, inventory as i
where p.item = i.item;
quit;
The table aliases are defined in the FROM clause.
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:
Notes:
- Using a table alias is usually optional. It helps making the code easier to understand.
- 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;
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.
Exercise
Copy and run the code from the yellow box below:
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:
- Rename the PRICE column from the AMAZON table as AMAZONPRICE.
- Rename the PRICE column from the LOCAL table as LOCALPRICE.
- Create a new column that contains the lower price of the two prices. Name the column as LOWERPRICE.
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.