PROC SQL : ALTER TABLE and UPDATE COLUMN

Live Online Training : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects
- Scenario Based Questions
- Job Placement Assistance
- Get 10% off till Oct 26, 2017
- Batch starts from October 28, 2017

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

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

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