This article will show you how to insert or add rows to a table. It is simple to accomplish using the PROC SQL INSERT INTO statement.
The “PROC SQL insert Into” statement is used to insert data into a table.
The following SQL statement will insert a new row into the table “CUSTOMERS”.
insert into customers values ('John Smith', '123 Main Street', 'New York');
The “INSERT INTO” statement is used to add a new row to the table. The statement consists of three parts:
- The values that are being inserted
- The name of the table where the data is being inserted
- A list of column names and their corresponding values.
Insert Rows based on Column Position by using the Values Clause
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.
You must specify values in the order in which the columns appear in the table.
PROC SQL; INSERT INTO newclass VALUES ("Alice", "M", 28, 150, 77) VALUES ("James", "M", 58, 155, 70); QUIT;
NOTE: 2 rows were inserted into WORK.NEW CLASS.
Insert Rows based on Column Name
You can include the target column name after the table name in the INSERT TABLE statement with a VALUES clause. The list can consist of the names of all or only a subset of columns in the table.
If an optional list of target column names is specified, only those columns are given values by the Insert statement. Target columns can be listed in any order, regardless of their position in the table. Any columns in the table but not listed are given missing values in the inserted rows.
proc sql; insert into newclass (name, sex, age, height, weight) values ("alice", "m", 28, 150, 77) values ("james", "m", 58, 155, 70); quit;
Inserting Rows By Using the SET Clause
The SET clause is used to assign column values by name. In the SET clause, the columns can be listed in any order.
proc sql; insert into newclass SET name="", sex="m", age=12, height=150, weight=77 SET name="James", sex="m", age=16, height=150, weight=77; quit;
Using Proc SQL Insert Into with a Query
The quickest method for inserting rows of data into a table is to use a query to select existing rows from one or more tables (or views) and insert them into another table.
You can insert rows using PROC SQL Insert into statement from a query result into a table that is either empty or already includes rows of data.
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 which the columns appear in the table.
If you specify a column, list values are inserted in the order in which the columns appear in the list.
proc sql; insert into newclass select * from sashelp.class where age > 12; quit;
NOTE: 12 rows were inserted into WORK.NEW CLASS.
Bulk Loading Data from Microsoft Excel
The following example illustrates assigning the Excel LIBNAME engine to perform a bulk-loading of the PURCHASES spreadsheet file into the PURCHASES table with the SQL procedure.
Note: A separate SAS/ACCESS software for PC files license is required to use the Excel libname engine.
libname myxls xlsx "/home/9to5sas/inputs/cars.xlsx"; proc sql; insert into cars select * from myxls.cars; quit;
NOTE: 428 rows were inserted into WORK.CARS.
Relate Articles: Creating Tables using Proc SQL