PROC SQL : ALTER TABLE and UPDATE COLUMN

Deepanshu Bhalla 1 Comment ,

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.

ALTER TABLE Syntax

Below is the syntax of ALTER TABLE in PROC SQL procedure in SAS.

ALTER TABLE table-name
ADD CONSTRAINT constraint-name constraint-definition
ADD column-definition
DROP CONSTRAINT constraint-name
DROP columns
DROP FOREIGN KEY constraint-name
DROP PRIMARY KEY
MODIFY column-definition
Example 1 : Adding Columns

In the following program, we have created a sample dataset named 'temp' to explain examples in this tutorial. In the dataset, we are adding 3 columns - Section as character variable, TotalMarks as numeric variable, DateOfBirth as Date format variable. The new columns would be blank.

data temp;
set sashelp.class;
run;

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;
PROC SQL: 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 Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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.

1 Response to "PROC SQL : ALTER TABLE and UPDATE COLUMN"
  1. Hi Deepanshu,

    I have a challenge in SQL for which I've never found an effective solution. I'd like to modify an existing SAS data set in a way that not only reduces the length of a character variable within it to the length of its largest value, but also removes the format and/or informat from that variable. If the length of the variable is minimized but the variable's format still specifies the original large value, then PROC PRINT and other procedures will still write messages to the SAS log saying that the variable's values were truncated when written to the output report. The only way that I've been able to do this so far is through a data step that reads and rewrites the data set with variable attributes adjusted, which also includes the instructions FORMAT _ALL_ and INFORMAT _ALL_. I'd prefer to be able to use PROC SQL in a way that not only uses the clause MODIFY CHAR (&length_of_largest_value) in an ALTER TABLE instruction to resize the variable, but also removes its format. I'd much prefer to have no format at all rather than a format that has been modified to fit the new length of the variable, e.g. $CHAR53. Is there a way to do this in PROC SQL?

    Thank you,
    Richard

    ReplyDelete
Next → ← Prev