Sentry Page Protection
Proc SQL II [11-17]

Creating SAS Tables using Proc SQL
So far, we have learned how to display a query result from the SQL procedure.

The results are displayed as the SAS outputs. However, no SAS table was created.

In this section, you will learn how to create actual SAS tables that will appear in your SAS library.

There are three ways to create a SAS table in Proc SQL.

You can create a SAS table by simply defining its columns.

​Let's look at an example.
Proc sql;
create table profile
  (Name char,
   age num);
quit;

The CREATE TABLE statement tells SAS to create a SAS table:

​PROFILE is the name of the table:

In this PROFILE table, the NAME and AGE columns are defined:

When defining columns, you must specify the column type.

In our example, the NAME column is defined as a character and the AGE column is defined as numeric:

The code creates an empty table with the two columns defined.

What's the point of creating an empty table?

Sometimes, you will have to create SAS tables where the data are updated on a regular basis.

For example, a retail company needs to keep their sales records that are updated in real-time.

A grocery store has to keep track of their inventory on a daily basis.

Creating an empty table allows you to ensure the table structure is correct before you insert data into the table.

More examples will be demonstrated in later sections.

Defining Column Length and Format

Similar to the data step, you can define the column length and assign a format to a column.​

Example
Proc sql;
create table profile
  (Name char (30),
   age num,
   dob num format=date9.);
quit;

The default length of a SAS column is eight.

As we have seen many times in the past, character values often get truncated when the length of eight is insufficient to capture the entire value.

The length of the column can be adjusted when defining the column:

Note: the length of a numeric column cannot be adjusted in Proc SQL.

In our example, the AGE column is defined as a numeric column. Its length cannot be changed.

The DOB column is created to capture the subjects' date of birth.

A (date9.) format is assigned to the column:

The table created is again an empty table with three columns:

Sometimes, you might not want to define the columns individually when creating the table.

Instead, you can create the table using the structure of an existing table.

Example
Proc sql;
create table cars2
  like sashelp.cars;
quit;

​The LIKE clause in this example tells SAS to create the CARS2 table using the structure of the sashelp.cars table.

The CARS2 table, although empty, has the same list of columns as the sashelp.cars table:

Last but not least, you can use the DESCRIBE table statement to examine the structure of a table.

Example
Proc sql;
describe table sashelp.cars;
quit;

The DESCRIBE statement tells SAS to describe the table structure and the column attributes in the Log window:

In the SAS log, you will see how the column attributes are defined for each column.
Exercise

Create an empty SAS table that contains the following columns:
  • ORDERNO
  • ITEM
  • QUANTITY
  • DATE
  • PRICE

The ORDERNO column should be a character column with a length of 12.

The ITEM column should be a character column with a length of 200.

The QUANTITY column should be a numeric column.

The DATE column should also be a numeric column with a (yymmdd10.) date format.

The PRICE column should be a numeric column with a (dollar8.) format.

Name the table as MASTERSALES.
Need some help? 


HINT:
Define the length of the character columns by specifying the length within a bracket.


SOLUTION:

Proc sql;
create table mastersales
(orderno char (12),
item char (200),
quantity num,
date num format=yymmdd10.,
price num format=dollar8.);
quit;

Fill out my online form.