Sentry Page Protection
Proc SQL II [12-17]

Creating SAS Tables using Query
A SAS table can also be created using a Proc SQL query.

​Let's look at an example.
Proc sql;
Create table class as
select *
  from sashelp.class;
quit;

In this example, the CLASS table is created from the query that selects all of the rows from the sashelp.class table.

The CLASS table is created as a carbon-copy of the sashelp.class table.

Let's look at another example.
Proc sql;
create table audi_bmw as
select make, mean(msrp) as mean_msrp
  from sashelp.cars
  where make in ("Audi" "BMW")
  group by make;
quit;

The query above computes the average MSRP for Audi and BMW:

The CREATE TABLE statement then creates the AUDI_BMW table based on the query result:

The AUDI_BMW table contains the average msrp for the two car makers:

In Proc SQL query, you are allowed to create two columns that have the same name, as long as no SAS table is created.

When creating an actual SAS table, SAS does not allow columns with the same name to exist in the same table.

You must rename or drop one of the columns before creating the table.

​Let's look at an example.

Copy and run the code from the yellow box below:

The DEMO1 and DEMO2 tables contain the age and gender information about John, Mary and Peter.

DEMO1

DEMO2

We are going to run a Proc SQL query to inner join the two tables:
Proc sql;
select *
from demo1, demo2
where demo1.name = demo2.name;
quit;

The result is displayed in the output:

Note: the query itself does not create an actual SAS table. 

It simply displays the result in the output.

In addition, there are two NAME columns in the output.

Now, let's create a SAS table using the same query.
Proc sql;
create table demo as
select *
from demo1, demo2
where demo1.name = demo2.name;
quit;

The DEMO table created contains only one NAME column:

There is also a warning message on the SAS log:
​
WARNING: Variable name already exists on file WORK.DEMO.

The issue here is that SAS does not allow tables to have two columns with the same name.

One of the columns is automatically removed.

One way to resolve this issue is to manually select the column to be included in the output.

Example
Proc sql;
create table demo as
select demo1.name, age, gender
from demo1, demo2
where demo1.name = demo2.name;
quit;

In this example, only one NAME column is selected from the DEMO1 table:

You will have the same result as before. However, there will be no warning message on the SAS log.
Exercise

Locate the SHOES table from the SASHelp library.

Write a Proc SQL step to compute the average sales for each product (e.g.  boots, men's casual, men's dress...etc.).

​Save the result in a SAS table and name the table as AVGSALES.
Need some help? 


HINT:
You can first write the query that computes the average sales for each product. Use the CREATE TABLE statement to save the results in an output table.


SOLUTION:

Proc sql;
create table avgsales as
select product, mean(sales) as avgsales
from sashelp.shoes
group by product;
quit;

Fill out my online form.