Proc SQL II 1.3

Search the site...

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:

Data Product;
Input Item $ Price;
Datalines;
Desktops 800
Cables 5
Monitors 300
Laptops 900
Phone 600
;
Run;

Data Inventory;
Input Item $ Inventory;
Datalines;
Desktops 20
Cables 500
Monitors 30
Laptops 40
Phone 50
;
Run;

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;

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.

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;

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:

Data Amazon;
Infile datalines dsd;
Input Teas : $12. price;
Datalines;
White Teas,2.99
Green Teas,3.99
Oolong Teas,6.99
Black Teas,5.99
Pu'er Teas,8.99
;
Run;

Data Local;
Infile datalines dsd;
Input Teas : $12. price;
Datalines;
Oolong Teas,3.99
Black Teas,7.99
Green Teas,5.99
Pu'er Teas,10.99
White Teas,4.99
;
Run;

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.