Don’t Miss Out on the Benefits of PROC SQL Create Table in SAS

In this comprehensive guide, we’ll delve into the nitty-gritty of using PROC SQL to create tables in SAS. From basic syntax to advanced techniques, we’ve got you covered.

The proc SQL creates 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.

Create a Table from Scratch

The code below demonstrates how to create a three-column table using proc SQL create table statement:

/* Create an empty table */
proc sql;
	create table NewTable (
	ID int, 
	Name char(20),
	Age int
	);

	/* Insert data into the table */
	insert into NewTable 
	values (1, 'Alice', 30) 
	values (2, 'Bob', 25) 
	values (3, 'Charlie', 35);
	select * from NewTable;
quit;

In this example, the create table command initiates the table creation. The subsequent lines define the columns and their data types. The insert into command then populates the table with sample data.

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

The SAS log validates the creation of the table.

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

Using SAS data set options to create an empty table

In SAS, you can create an empty table with a subset of columns from an existing table using data set options. One common method is to use the KEEP= or DROP= data set options along with the DATA step. These options allow you to specify which columns you want to keep or drop when creating the new table.

Using the KEEP= Option

The KEEP= option allows you to specify the columns you want to keep in the new table. Here’s how you can use it:

data NewTable (keep = ID Name);
set OldTable;
stop;
run;

In this example, the NewTable will be created with only the Name columns from the sashelp.class dataset. The stop; statement ensures that the table is empty by not reading any records from the OldTable.

Using the DROP= Option

Alternatively, you can use the DROP= option to specify the columns you want to exclude from the new table:

data NewTable (drop= weight);
set OldTable;
stop;
run;

In this example, all columns except weight will be included in the NewTable. Again, the stop; statement ensures that the table is empty.

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 with a subset of columns

You can also use PROC SQL to create an empty table with a subset of columns. However, this method usually involves selecting zero rows to ensure the table is empty:

proc sql;
create table NewTable as select Name,age from sashelp.class where 0=1;
quit;

In this SQL example, the where 0=1 condition ensures that no rows are selected, resulting in an empty table with only the Name and age columns in the sashelp.class dataset.

or you can use the SQL Like Operator to create a similar table.

proc sql;
	create table work.NewTable
(keep=name age) like sashelp.class;
quit;

NOTE: Table WORK.NEWTABLE created, with 0 rows and 2 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. 

proc sql;
  create table NewTable (
    ID int,
    Name char(20),
    Age int
  );
quit;

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:

CHARACTER (or CHAR) | VARCHAR | INTEGER (or INT) | SMALLINT | DECIMAL (or DEC) | NUMERIC (or NUM) | FLOAT | REAL | DOUBLE PRECISION | DATE.

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;

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;

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 a table using proc SQL create table statement.

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