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 “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  

SAS can access and read Excel spreadsheet files with a LIBNAME statement. This allows worksheet files in an Excel file to be processed in the same way as 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

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share via
Copy link
Powered by Social Snap