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.

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';

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;

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;

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 NEWCOUNTRIES. 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 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;

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:

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
      from sql.newcountries;

Rows Inserted with a Query

If your query does not return data for every column, then you receive an error message, and the row is not inserted. For more information about how PROC SQL handles errors during data insertions, see Handling Update Errors.