SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

Inserting Rows into Tables

Use the INSERT statement to insert data values into tables. The INSERT statement first

adds a new row to an existing table, and then inserts the values that you specify into the

row. You specify values by using a SET clause or VALUES clause. You can also insert

the rows resulting from a query. Under most conditions, you can insert data into tables

through PROC SQL and SAS/ACCESS views. For more information, see “Updating a

View” on page 132.

Inserting Rows with the SET Clause

With the SET clause, you assign values to columns by name. The columns can appear in

any order in the SET clause. The following INSERT statement uses multiple SET

clauses to add two rows to NewCountries:

libname sql 'SAS-library';

/* Create the newcountries table. */

proc sql;

create table sql.newcountries

like sql.countries;

/* Insert all of the rows from countries into newcountries based */

/* on a population of 130000000. */

proc sql;

insert into sql.newcountries

select * from sql.countries

where population ge 130000000;

/* Insert 2 new rows in the newcountries table. */

/* Print the table. */

proc sql;

insert into sql.newcountries

set name='Bangladesh',

capital='Dhaka',

population=126391060

set name='Japan',

capital='Tokyo',

population=126352003;

title "World's Largest Countries";

select name format=$20.,

capital format=$15.,

population format=comma15.0

from sql.newcountries;

116 Chapter 4 • Creating and Updating Tables and Views

Output 4.2 Rows Inserted with the SET Clause

Note the following features of SET clauses:

• As with other SQL clauses, use commas to separate columns. In addition, you must

use a semicolon after the last SET clause only.

• If you omit data for a column, then the value in that column is a missing value.

• To specify that a value is missing, use a blank in single quotation marks for character

values and a period for numeric values.

Inserting Rows with the VALUES Clause

With the VALUES clause, you assign values to a column by position. The following

INSERT statement uses multiple VALUES clauses to add rows to NewCcountries.

Recall that NewCountries has six columns, so it is necessary to specify a value or an

appropriate missing value for all six columns. See the results of the DESCRIBE TABLE

statement in “Creating Tables like an Existing Table” on page 115 for information about

the columns of NewCountries.

libname sql 'SAS-library';

proc sql;

insert into sql.newcountries

values ('Pakistan', 'Islamabad', 123060000, ., ' ', .)

values ('Nigeria', 'Lagos', 99062000, ., ' ', .);

title "World's Largest Countries";

select name format=$20.,

capital format=$15.,

population format=comma15.0

from sql.newcountries;

Inserting Rows into Tables 117

Output 4.3 Rows Inserted with the VALUES Clause

Note the following features of VALUES clauses:

• As with other SQL clauses, use commas to separate columns. In addition, you must

use a semicolon after the last VALUES clause only.

• If you omit data for a column without indicating a missing value, then you receive an

error message and the row is not inserted.

• To specify that a value is missing, use a space in single quotation marks for character

values and a period for numeric values.

Inserting Rows with a Query

You can insert the rows from a query result into a table. The following query returns

rows for large countries (more than 130 million in population) from the Countries table.

The INSERT statement adds the data to the empty table NewCountries, which was

created earlier in “Creating Tables like an Existing Table” on page 115:

libname sql 'SAS-library';

proc sql;

create table sql.newcountries

like sql.countries;

proc sql;

title "World's Largest Countries";

insert into sql.newcountries

select * from sql.countries

where population ge 130000000;

select name format=$20.,

capital format=$15.,

population format=comma15.0

118 Chapter 4 • Creating and Updating Tables and Views