The ALTER TABLE statement is used to add new variables, delete existing variables, or modify format of variables.
This method gives you more flexibility while adding new variables or modifying existing ones. It is also possible to add values on those newly added columns with UPDATE TABLE statement.
The following example demonstrates that two new columns new_char (a character variable) and new_num (a numeric variable) have been added in sas_dataset with blank values.
By default the character variable has a blank value (‘ ‘) and the numeric variable has dot (.) as blank value. Then after we have added data in those newly added variables using the UPDATE TABLE statement.
/* create a data set */
data sas_dataset;
input platform $ id;
datalines;
Facebook 100
Twitter 500
LinkedIn 300
Snapchat 200
;
proc print;
run;
Add New Variables:
/* add new variables */
proc sql;
ALTER TABLE sas_dataset add
new_num NUM (8),
new_char CHAR (20),
DOB num informat=date9. format=date9.;
quit;
Update data set and assign values
/* update data set and assign values */
proc sql;
UPDATE sas_dataset
set new_num=id*1000,
new_char='Online Channel',
DOB='01FEB2008'd;
quit;
Did you see any problem with the above data set?
The same value has been assigned to all the rows. You can also add conditional data here but for simplicity we have just added the same values for all the rows.
Update SAS Data set: Using WHERE clause
When you use the where clause you have to be super careful because all the variables added in the update statement will be updated according to the where clause.
In the below example it can be easily misunderstood that where clause is only applied on DOB column but that’s not the case. It is applicable for all the variables mentioned after the SET statement.
data sas_dataset;
input platform $ id;
datalines;
Facebook 100
Twitter 500
LinkedIn 300
Snapchat 200
;
proc print;
run;
/* add new variables */
proc sql;
ALTER TABLE sas_dataset add new_num NUM (8), new_char CHAR (20), DOB num
informat=date9. format=date9.;
quit;
/* update data set using where clause */
proc sql;
UPDATE sas_dataset set
new_num=id*1000,
new_char='Online Channel',
DOB='01MAR2008'd
where id in (100, 300);
quit;
proc print;
Update SAS Data set: Using CASE statement
This is an efficient way to update variables by adding multiple conditions. It is not possible with the where clause in the update statement.
You can have multiple update statements in the same proc sql statement. Both the update statement gets executed independently but sequentially as it appears in the proc sql statement.
/* update SAS Data set using CASE statement */
proc sql;
UPDATE sas_dataset
SET new_num=id*1000,
new_char='Online Channel';
UPDATE sas_dataset
SET DOB= CASE WHEN id in (100, 300) THEN '01MAR2008'd
WHEN id in (200, 500) THEN '15APR2010'd
ELSE '01JAN9999'd
END;
quit;
/* print data set values */
proc print data=sas_dataset;
run;