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

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


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

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

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


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
Picture

DEMO2
Picture

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

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

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

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

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