How can I use Proc SQL to enter data into a table?

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 following SQL statement will insert a new row into the ” CUSTOMERS ” table.

The “PROC SQL insert Into” statement is used to insert data into a table.

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 used in a query: SELECT, FROM, and any optional clauses, such as ORDER BY. 

Values from the query result are inserted into columns where 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  

SAS can access and read Excel spreadsheet files with a LIBNAME statement. This allows worksheet files in an Excel file to be processed the same way SAS data sets are processed in a SAS library. 

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