Ever wondered how to insert data into tables using PROC SQL in SAS? You’re in the right place. Today, we’re breaking down the ins and outs of using the PROC SQL INSERT INTO statement. By the end of this guide, you’ll be inserting data like a pro. Let’s dive in!
The Basics: What is PROC SQL Insert INTO?
In a nutshell, PROC SQL INSERT INTO allows you to add new rows to a table. Simple, right? But wait, there’s more to it. Let’s break it down:
Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
proc sql;
INSERT INTO students (name, age)
VALUES ('John', 25);
quit;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.
Wrapping Up
And there you have it! A complete guide to using PROC SQL INSERT INTO in SAS. Now you’re equipped to insert data into tables like a pro.
Want more SAS tips? Stay tuned for more tutorials and guides!
Relate Articles: Creating Tables using Proc SQL
