PROC SQL : ALTER TABLE and UPDATE COLUMN

This tutorial explains how to add or delete columns in a table and update column values with PROC SQL.

The ALTER TABLE statement is used to add new columns, delete existing columns or modifying the format of columns.

The UPDATE statement is used to modify existing column values in a table.

Create a Dataset

data temp;
set sashelp.class;
run;

ALTER TABLE Syntax

ALTER TABLE table-name
ADD CONSTRAINT constraint-name constraint-definition
ADD column-definition
DROP CONSTRAINT constraint-name
DROP column(s)
DROP FOREIGN KEY constraint-name
DROP PRIMARY KEY
MODIFY column-definition

Example 1 : Adding Columns

In the following program, we are adding 3 columns - Section as character variable, TotalMarks as numeric variable, DateOfBirth as Date format variable. The new columns would be blank.

PROC SQL;
ALTER TABLE temp ADD Section CHAR (10), TotalMarks NUM (8),
DateOfBirth num informat=date7. format=date7.;
QUIT;

ALTER Table : Add Columns

Example 2 : Add Values in New Columns

The UPDATE statement is used to add or update values in columns. In this case, we are updating rows wherein age is less than 15.

PROC SQL;
UPDATE temp SET Section='Section A', TotalMarks=100, DateOfBirth='22OCT99'D where age < 15;
QUIT;

Update Columns

Example 3 : Conditional Update Statement

We are adding 5 to column Height if age is less than or equal to 15. If age is greater than 15, height should be added by 10. In other words, we are using IF THEN ELSE conditions in UPDATE statement.

PROC SQL;
UPDATE temp
SET Height =
CASE WHEN age <= 15 THEN Height + 5
WHEN age > 15 THEN Height + 10
ELSE HEIGHT
END;
QUIT;

Example 4 : Update Multiple Columns

We can update multiple columns with UPDATE statement like the programs written below -

PROC SQL;
ALTER TABLE temp ADD min_age num , min_height num;
UPDATE temp
SET min_age = (SELECT MIN(age) FROM temp2),
min_height = (SELECT MIN(height) FROM temp2); 
QUIT;

PROC SQL;
UPDATE temp SET Section='SectionB', DateOfBirth='22OCT02'D where age<15;
UPDATE temp SET Section='SectionA', DateOfBirth='22OCT99'D where age>=15;
QUIT;

Example 5 : Modify the column attributes

We can modify the column format with MODIFY statement.

PROC SQL;
ALTER TABLE temp
MODIFY totalmarks DECIMAL(8,2) format=8.2;quit;

Example 6 : Delete Columns

PROC SQL;
ALTER TABLE temp DROP totalmarks, section;
QUIT;

Example 7 : Adding NOT NULL Constraint

We are preventing missing values in a column using NOT NULL Contraint.

PROC SQL;
ALTER TABLE TEMP
ADD CONSTRAINT NOT_NULL_WEIGHT NOT NULL(WEIGHT);QUIT;

Example 8 : Adding CHECK Constraint

We are validating column values with CHECK constraint.See the example below -

PROC SQL;
ALTER TABLE PRODUCTS
ADD CONSTRAINT CHECK_SECTION
CHECK (SECTION IN ('Section A', 'Section B'));
QUIT;

Example 9 : Allowing only UNIQUE values

We are not allowing duplicate values in a column.

PROC SQL;
CREATE TABLE TEMP3
(ID NUM UNIQUE,
STATE CHAR(20));
QUIT;

Example 10 : Creating a Primary Key

The PRIMARY KEY constraint uniquely identifies each record in a table.

PROC SQL;
ALTER TABLE TEMP3
ADD CONSTRAINT PRIM_KEY PRIMARY KEY (ID);
QUIT;

Related Article : How to Insert Rows with PROC SQL 

Proc SQL Tutorials : 15 Proc SQL Tutorials

Spread the Word!
Share Share Tweet Subscribe

Related Posts

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 has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.

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