Sentry Page Protection
Proc SQL II [13-17]
Inserting Rows Into a Table
Sometimes, you will have to regularly update the SAS tables in your system.
You will have to insert new rows, update particular fields or even delete rows from the table.
These can all be done using the SQL procedure.
Let's look at an example.
Copy and run the code from the yellow box below:
You will have to insert new rows, update particular fields or even delete rows from the table.
These can all be done using the SQL procedure.
Let's look at an example.
Copy and run the code from the yellow box below:
The CLIENT table contains a list of clients for a dental clinic. It contains four columns:
- FIRST
- LAST
- AGE
- INSURANCE
There is a new walk-in client coming in today. You'd like to insert a new row into the table for this new client.
Let's look at how you can insert the new data into the table.
Method 1: SET Clause
Proc sql;
create table client_new as
select * from client;
insert into client_new
set first="Mellie",
last ="Weyer",
age = 28,
insurance = "Yes";
quit;
create table client_new as
select * from client;
insert into client_new
set first="Mellie",
last ="Weyer",
age = 28,
insurance = "Yes";
quit;
We will first copy the CLIENT table into the CLIENT_NEW table.
This is optional.
Next, we will use the INSERT statement to tell SAS to insert rows into the table:
Finally, the SET clause specifies the values for each column to be inserted into the table.
The new row is now inserted into the CLIENT_NEW table:
Inserting Multiple Rows Into a Table
You can also insert multiple rows of data into a SAS table using multiple SET clauses.
For example, you have two new clients that you need to insert into the CLIENT_NEW table:
- Mellie Weyer
- Raeann Kuchta
Let's look at how you can insert both clients to the table:
Proc sql;
create table client_new as
select * from client;
insert into client_new
set first="Mellie",
last ="Weyer",
age = 28,
insurance = "Yes"
set first="Raeann",
last =" Kuchta",
age = 40,
insurance = "No";
quit;
create table client_new as
select * from client;
insert into client_new
set first="Mellie",
last ="Weyer",
age = 28,
insurance = "Yes"
set first="Raeann",
last =" Kuchta",
age = 40,
insurance = "No";
quit;
In this example, the INSERT statement contains two SET clauses. Each clause inserts a new row into the table.
The two new clients are now included in the CLIENT_NEW table:
VALUES Clause
Another way to insert rows into a SAS table is to use the VALUES clause.
Let's look at an example.
Proc sql;
create table client_new as
select * from client;
insert into client_new
(first, last, age, insurance)
values ("Chet", "Krasner",
30, "Yes")
values ("Mirna", "Bonnell",
26, "No");
quit;
create table client_new as
select * from client;
insert into client_new
(first, last, age, insurance)
values ("Chet", "Krasner",
30, "Yes")
values ("Mirna", "Bonnell",
26, "No");
quit;
In this example, we specified the four column values to be inserted into the new row:
- FIRST
- LAST
- AGE
- INSURANCE
The VALUES clauses then insert the two rows of data into the table:
The two new clients are now inserted into the CLIENT_NEW table:
Finally, you can also insert rows into the table using Proc SQL query.
Copy and run the code from the yellow box below:
The WALKIN table contains two new walk-in clients to be inserted into the CLIENT_NEW table.
We will now insert these new clients into the table, again using the INSERT INTO statement.
Proc sql;
create table client_new as
select * from client;
insert into client_new
select * from walkin;
quit;
create table client_new as
select * from client;
insert into client_new
select * from walkin;
quit;
The new clients are now added to the output table:
In the next few sections, you will learn how to update rows, and delete rows from a table.
Exercise
Copy and run the code from the yellow box below:
Copy and run the code from the yellow box below:
The code above creates two tables:
- EMPLOYEE
- NEWHIRE
The EMPLOYEE table contains a list of current employees. The NEWHIRE table contains two new hires.
Insert the new hires into the EMPLOYEE table. The starting annual salary for the two new hires is $50,000. Both are expected to start on January 1, 2017.
Need some help?
HINT:
You must manually specify the order of the columns to be added to the EMPLOYEE table. The salary and start date data must also be entered manually in the SQL procedure.
SOLUTION:
Proc sql;
insert into employee
select trim(first) || " " || trim(last) as name,
sex as gender,
'01JAN2017'd as startdate,
50000 as salary
from newhire;
quit;
Fill out my online form.