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
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 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
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.
Hi Deepanshu,
ReplyDeleteI 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
This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information... https://view.ly/v/I6i2zHGxK4JY
ReplyDelete