Proc Sql Create Table 9TO5SAS

This article will show you how to create tables using Proc SQL.

The proc SQL create table statement allows you to create tables without rows from column definitions or tables from query results. You may also use “Create Table” to duplicate an existing table.

Page Contents

Proc SQL Create Table Like Another Table

Using the LIKE clause in the CREATE TABLE command, you can create an empty table with the same columns and attributes as an existing table or view.

proc sql;
	create table newclass like sashelp.class;
quit;

The new table contains zero rows of data but does include all the column definitions without any rows of data from an existing table or view.

The SAS log validates the creation of the table.

NOTE: Table WORK.NEWCLASS created, with 0 rows and 5 columns.

Copying an Existing Table

To copy an existing table, use the CREATE TABLE command with a query that returns the entire table rather than selecting columns and rows.

The following PROC SQL step creates the new table Work.class, which is an exact duplicate of the source table sashelp.class.

proc sql;
	create table class as select * from sashelp.class;
quit;

NOTE: Table WORK.CLASS created, with 19 rows and 5 columns.

Creating an Empty Table By Defining Columns

Use the CREATE TABLE statement with column specifications for the columns you want. This statement creates a table without rows (an empty table).

You can create a table with 0 rows using a proc SQL create table statement with column specifications.

A column specification consists of a column name (required), a data type (required), a column width (optional), one or more column modifiers (optional), and a column constraint (optional).

Specifying data types

When you define columns in a table, you must declare a data type for each column after the column name:

column-name data-type <(column-width)> <column-modifier-1<...column-modifier-n>>

Data-type is enclosed in parentheses and specifies one of the following:

proc sql;
	create table class 
(name, age num ,weight float, Bdate date);
quit;

The SAS dataset uses two data types: numeric and character. However, PROC SQL supports additional data types. Many SQL-based data types are supported in SAS, but not all.

Reference – Difference between CHAR and VARCHAR.

Therefore, in the CREATE TABLE statement, you can specify any of the ten different data types. PROC SQL converts the supported data types that are not SAS data types to either numeric or character format when the table is created.

NOTE: Table WORK.STUDENTS created, with 0 rows and 4 columns.

Specifying Column Widths

The default column width in SAS is 8 bytes for both character and numeric columns. However, character and numeric data values are saved differently:

Character data is stored as one character per byte.
Numeric data is saved as floating-point numbers in real binary format, allowing for 16- or 17-digit precision inside 8 bytes.

PROC SQL enables you to specify a column width for character columns but not numeric columns.

proc sql;
	create table class 
(name char(10), age num , weight float, Bdate date);
quit;

Specifying Column Modifiers

A column specification might include one or more SAS column modifiers in the CREATE TABLE statement: INFORMAT=, FORMAT=, and LABEL=. Column modifiers, if used, are specified at the end of the column specification.

Note: A fourth SAS column modifier, LENGTH=, is not allowed in a CREATE TABLE clause. However, we can use it in a SELECT clause.

proc sql;
	create table class 
(name char(10), age num , weight float, Bdate date format=date9.);
quit;

Specifying a Subset of Columns from a Table

Use the SAS data set options DROP= or KEEP= to construct an empty table that contains just a specified subset of columns from an existing table.

proc sql;
	create table work.baseball 
(keep=name team) like sashelp.baseball;
quit;

Creating a Table from a Query Result

Sometimes, you want to create a new table containing both columns and rows derived from an existing table or set of tables.

To create a PROC SQL table from a query result, use a CREATE TABLE statement with the AS keyword, and place it before the SELECT statement.

proc sql;
	create table work.payroll as select dateofbirth, dateofhire, jobcode, salary 
		from sasuser2.payrollmaster where jobcode contains 'TA';
quit;

See the structure of a table.

When you create a table, the CREATE TABLE statement sends a message to the SAS log indicating the number of rows and columns in the table. That message, however, has no information regarding column attributes.

You can use the DESCRIBE TABLE command in PROC SQL to show a list of columns and column attributes for one or more tables in the SAS log, regardless of whether the tables contain rows of data.

proc sql;
	describe table work.payroll;
quit;

proc SQL create table — structure of a table

Other SAS procedures, such as PROC CONTENTS, can be used instead of the DESCRIBE TABLE statement to list a table’s columns and column characteristics.

Instead of writing a message to the SAS log as the DESCRIBE TABLE statement does, PROC CONTENTS creates a report.

We hoped this article helped you to use in creating table using proc SQL create table statement.

You may also want to see our post on Intermediate Proc SQL Tutorials with Examples.

Creating Tables using Proc SQL