PROC SQL : INSERT INTO

This tutorial explains how to insert or add rows in the same table. It can be easily done with INSERT INTO statement of PROC SQL.

Create a Sample Dataset

The following SAS program creates a dataset which will be used to explain examples in this tutorial.

data temp;
set sashelp.class;
run;

1. Insert Rows based on Column Position

With the VALUES clause and INSERT statement, we can assign values to columns by their positions. In the example below, "Sam" would be added to the first column, "M" added to the second column, "28" added to the third column and so on. Multiple VALUES clauses implies multiple rows to be added into the table.

PROC SQL;
INSERT INTO temp
VALUES ("Sam","M",28,75,100)
VALUES ("Sam2","M",58,55,70);
QUIT;

See the log shown in the image below -

2. Insert Rows based on Column Name

We can also define columns and values assigned to them only. Values of all the columns that are not defined would be assigned missing.

PROC SQL;
INSERT INTO temp (name,sex)
VALUES ("Sam","M");
QUIT;

3. Insert Rows with a Query

We can also add rows with a query. In the example below, we are appending rows to the table by extracting data from the other table.

proc sql;
insert into newclass
select * from class
where score > 150;
quit;

4. Create Sample Data with PROC SQL

The DATALINES statement with an INPUT statement in DATA STEP is used to read data that you enter directly in the program. In PROC SQL, you can do the same with CREATE TABLE and INSERT INTO statement.

proc sql;
create table list
(ID num(10), Gender char(1),Salary num,
DateOfBirth num informat=date7. format=date7.);
insert into list
values(12345,'F',42260,'21JAN01'd)
values(23456,'M',61090,'26JAN54'd);
quit;

5. Add Constraints in the Table

We are adding constraints that values of ID variable should be unique (Primary Key), "area" variable contain only two values - USA and India, samplesize should be greater than 0.

proc sql;
create table example
(ID num(15),
samplesize num,
area  char(15) NOT NULL,
constraint prim_key    primary key(ID),
constraint samplesize  check(samplesize gt 0),
constraint area   check(area in ('USA', 'India')));
quit;

Let's insert two rows

proc sql;
insert into example
values(12345,42260,'India')
values(12345,61090,'USA');
quit;

It returns error due to duplicate values in a variable that have a constraint of primary key.

To check the table, we can display the structure of table using the DESCRIBE TABLE statement which is an alternative to PROC CONTENTS. In simple words, we are interested to see how table was created and the format of variables.

PROC SQL;
DESCRIBE TABLE EXAMPLE;
QUIT;

Related Article : How to Alter Table and Update Column

Related Posts

Proc SQL Tutorials : Top 15 Proc SQL Tutorials

Spread the Word!
Share Share Tweet

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn