Inserting Rows of Data into a Table

Overview

After you have created an empty table, you will want to insert rows of data. You might also want to insert additional rows of data into tables that already contain data. You can use the INSERT statement in three different ways to insert rows of data into existing tables, either empty or populated.
Note: You can also use the INSERT statement to insert rows of data in a single table that underlies a PROC SQL view. To learn more about PROC SQL views, see Creating and Managing Views Using PROC SQL.
Method of Inserting Row
Example
insert values by column name by using the SET clause 
proc sql;
    insert into work.discount
       set destination='LHR',
           begindate='01MAR2000'd,
           enddate='05MAR2000'd,
           discount=.33
       set destination='CPH',
           begindate='03MAR2000'd,
           enddate='10MAR2000'd,
           discount=.15;
insert lists of values by using the VALUES clause 
proc sql;
    insert into work.discount (destination,
            begindate,enddate,discount)
       values ('LHR','01MAR2000'd,
               '05MAR2000'd,.33)
       values ('CPH','03MAR2000'd,
              '10MAR2000'd,.15);
insert rows that are copied from another table by using a query result 
proc sql;
     insert into payrollchanges2
       select empid,salary,dateofhire
          from sasuser.payrollmaster
          where empid in ('1919','1350','1401'),
In each method, the INSERT statement inserts new rows of data into the table. To indicate that the rows have been inserted, the SAS log displays a message similar to the following.
Table 5.12 SAS Log 
NOTE: 1 row was inserted into WORK.DISCOUNT.
Here is information about how to use each of these methods to insert rows of data into a table.

Inserting Rows By Using the SET Clause

Sometimes you need to add rows of data to a table, but the data is not currently contained in any table. In this situation, you can use either the SET clause or the VALUES clause in the INSERT statement to specify the data to be added.
The SET clause in the INSERT statement enables you to specify new data to be added to a table. The SET clause specifies column names and values in pairs. PROC SQL reads each column name-value pair and assigns the value to the specified column. A separate SET clause is used for each row to be added to the table.
The syntax of the INSERT statement that contains the SET clause is shown below.
General form, INSERT statement containing the SET clause:
INSERT INTO table-name <(target-column-1<, ... target-column-n)>
SET column-1=value-1<, ... column-n=value-n>
<... SET column-1=value-1<, ... column-n=value-n>>;
Here is an explanation of the syntax:
table-name
specifies the name of the table to which rows are inserted.
target-column
specifies the name of a column into which data is inserted.
each SET clause
specifies one or more values to be inserted in one or more specified columns in a row. Multiple SET clauses are not separated by commas.
column
specifies the name of a column into which data is inserted.
value
specifies a data value to be inserted into the specified column. Character values must be enclosed in quotation marks.
multiple column=value pairs in a SET clause
are separated by commas.
Note: It is optional to include a list of target column names after the table name in the INSERT TABLE statement that includes a SET clause. The list can include the names of all or only a subset of columns in the table. If you specify an optional list of target column names, then you can specify values only for columns that are in the list. You can list target columns in any order, regardless of their position in the table. Any columns that are in the table but not listed are given missing values in the inserted rows.
Note: Although it is recommended that the SET clause list column-value pairs in order (as they appear in the table column list or the optional column list), it is not required.

Example

Consider the table Work.Discount, which was presented in the last topic. Work.Discount stores airline discounts for certain flight destinations and time periods in March. By submitting a DESCRIBE TABLE statement, you can see this table's columns and column attributes.
Table 5.13 SAS Log 
NOTE: SQL table WORK.DISCOUNT was created like:

create table WORK.DISCOUNT( bufsize=4096 ) 
  ( 
   Destination char(3),
   BeginDate num format=DATE9.,
   EndDate num format=DATE9.,
   Discount num 
  );
The following PROC SQL step does both of the following:
  • adds two rows of new data to Work.Discount by using an INSERT statement that contains two SET clauses, one for each row
  • generates a report that displays Work.Discount, with its two new rows, by using a SELECT statement.
In this situation, you do not need to include an optional list of column names. 
proc sql;
   insert into work.discount
      set destination='LHR',
          begindate='01MAR2000'd,
          enddate='05MAR2000'd,
          discount=.33
      set destination='CPH',
          begindate='03MAR2000'd,
          enddate='10MAR2000'd,
          discount=.15;
   select *
     from discount;
Output with Rows Inserted by the Set Clause

Inserting Rows By Using the VALUES Clause

The INSERT statement uses the VALUES clause to insert a list of values into a table. Unlike the SET clause, the VALUES clause does not specify a column name for each value, so the values must be listed in the correct order. Values must be specified in the order in which the columns appear in the table or, if an optional column list is specified, in the order in which the columns appear in that list. A separate VALUES clause is used for each row to be added to the table.
General form, INSERT statement containing the VALUES clause:
INSERT INTO table-name <(target-column-1<, ... target-column-n)>
VALUES (value-1<, ... value-n)>
<... VALUES (value-1<, ... value-n>)>;
Here is an explanation of the syntax:
table-name
specifies the name of the table to which rows are inserted.
target-column
specifies the name of a column into which data is inserted.
each VALUES clause
lists the values to be inserted in some or all columns in one row, which is enclosed in parentheses. Multiple VALUES clauses are not separated by commas.
value
specifies a data value to be added. Character values must be enclosed in quotation marks. Multiple values must be separated by commas. Values must be listed in positional order, either as they appear in the table or, if the optional column list is specified, as they appear in the column list.
Note: It is optional to include a list of target column names after the table name in the INSERT TABLE statement that includes a VALUES clause. The list can include the names of all or only a subset of columns in the table. If an optional list of target column names is specified, then only those columns are given values by the statement. Target columns can be listed in any order, regardless of their position in the table. Any columns that are in the table but not listed are given missing values in the inserted rows.
You can use the VALUES clause to insert a value for all or only some of the columns in the table.
If you want to ...
Then ...
Example
insert a value for all columns in the table
You can omit the optional list of column names in the INSERT statement.
PROC SQL
  • reads values in the order in which they are specified in the VALUES clause
  • inserts the values into columns in the order in which the columns appear in the table. 
insert into work.newtable
    values ('WI','FLUTE',6)
    values ('ST','VIOLIN',3);
insert a value for only some of the columns in the table
You must include a list of column names in the INSERT statement.
PROC SQL
  • reads values in the order in which they are specified in the VALUES clause
  • inserts the values into columns in the order in which the columns are specified in the column list. 
insert into work.newtable
        (item,qty)
    values ('FLUTE',6)
    values ('VIOLIN',3);
You must list a value for every column into which PROC SQL inserts values (as specified in either the table list or the optional list of column names). To specify that a value is missing, use a space enclosed in single quotation marks for character values and a period for numeric values. For example, the following VALUES clause specifies values to be inserted in three columns; the first two values are missing: 
values (' ', ., 45)
In this example, the first value specified is a missing value for a character column, and the second value is a missing value for a numeric column.

Example

Suppose you want to insert two more rows into the table Work.Discount, which stores airline discounts for certain flight destinations and time periods in March. In the previous section, you inserted two rows into Work.Discount by using the SET clause, so the table now looks like the following table.
Work.Discount
Add two more rows, by using the VALUES clause. The following PROC SQL step adds two rows of new data to Work.Discount and generates a report that displays the updated table: 
proc sql;
   insert into work.discount (destination, 
          begindate,enddate,discount)
      values ('ORD','05MAR2000'd,'15MAR2000'd,.25)
      values ('YYZ','06MAR2000'd,'20MAR2000'd,.10);
   select *
      from work.discount;
Work.Discount
The two rows that were just inserted by using the VALUES clause are the third and fourth rows above.
You might have noticed that the INSERT statement in this example includes an optional list of column names. In this example, data is being inserted into all columns of the table, and the values are listed in the order in which the columns appear in the table, so it is not strictly necessary to use a column list. However, including the list of column names makes it easier to read the code and understand what the code is doing.

Inserting Rows from a Query Result

The fastest way to insert rows of data into a table is to use a query to select existing rows from one or more tables (or views) and to insert the rows into another table. You can insert rows from a query result into either an empty table or a table that already contains rows of data. When you add rows of data to a table that already contains rows, the new rows are added at the end of the table.
To insert rows from a query result, use an INSERT statement that includes the clauses that are used in a query: SELECT, FROM, and any optional clauses, such as ORDER BY. Values from the query result are inserted into columns in the order in which the columns appear in the table or, if an optional column list is specified, in the order in which the columns appear in that list.
General form, INSERT statement containing query clauses:
INSERT INTO table-name <(target-column-1<, ... target-column-n)>
SELECT column-1<, ... column-n>
FROM table-1 | view-1<, ... table-n | view-n>
<optional query clauses>;
Here is an explanation of the syntax:
table-name
specifies the name of the table to which rows are inserted.
target-column
specifies the name of a column into which data is inserted.
SELECT
specifies the column(s) that is inserted.
FROM
specifies the table(s) or view(s) to be queried.
optional query clauses
are used to refine the query further. These include the WHERE, GROUP BY, and HAVING, clauses.
Note: It is optional to include a list of target column names after the table name in the INSERT TABLE statement that includes query clauses. The list can include the names of all or only a subset of columns in the table. If an optional list of target column names is specified, then only those columns are given values by the statement. Target columns might be listed in any order, regardless of their position in the table. Any columns that are in the table but not listed are given missing values in the inserted rows.

Example

A mechanic at a company has been promoted from level 2 to level 3, and you need to add this employee to Sasuser.Mechanicslevel3, a table that lists all level-3 mechanics. Create a temporary copy of Sasuser.Mechanicslevel3 called Work.Mechanicslevel3_New, and display the new table in a report: 
proc sql;
   create table work.mechanicslevel3_new as
      select *
         from sasuser.mechanicslevel3;
Work.Mechanicslevel3_New Table
Next, you insert a row into Work.Mechanicslevel3_New for the new level-3 employee, whose EmpID is 1653. This employee is currently listed in Sasuser.Mechanicslevel2, so your INSERT statement queries the table Sasuser.Mechanicslevel2. Your PROC SQL step ends with a SELECT statement that outputs the revised table Work.Mechanicslevel3_New to a report. 
proc sql; 
   insert into work.mechanicslevel3_new
      select empid, jobcode, salary 
         from sasuser.mechanicslevel2
         where empid='1653';
      select *
         from work.mechanicslevel3_new;
Revised Work.Mechanicslevel3_New Table
The row that you have inserted into Work.Mechanicslevel3_New is row 8 above. As you can see, the values for JobCode and Salary for the new employee have to be changed. Updating existing values in a table is covered later in this chapter.
Note: Although the new row is shown here at the bottom of the table, the order of rows in a PROC SQL query cannot be guaranteed if an ORDER BY clause is not used.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.146.221.149