O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required