I am trying to add a new column to a dataset based on the binary condition of another column. However, I keep getting an error. My code below:
proc sql;alter table dataadd Status (case WHEN missing(DeactReason) THEN 'Active' ELSE 'Inactive)'END;quit;However, I get the following error:
143 proc sql;144 alter table data145 add Status (case WHEN missing(DeactReason) THEN 'Active' ELSE 'Inactive)'-79ERROR 79-322: Expecting a CHECK.146 END;-79ERROR 79-322: Expecting a ).147 quit;Thanks!EDIT:Corrected the code:
proc sql;alter table tableadd Status (case WHEN missing(DeactReason) THEN 'Active' ELSE 'Inactive')END;quit;Get the following error now:
148 proc sql;149 alter table table150 add Status (case WHEN missing(DeactReason) THEN 'Active' ELSE 'Inactive')- -79 2276ERROR 79-322: Expecting a CHECK.ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=,<>, =, >, >=, ?, AND, BETWEEN, CONTAINS, END, EQ, EQT, GE, GET, GT, GTT, IN, IS,LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.ERROR 76-322: Syntax error, statement will be ignored.151 END;152 quit;Best Answer
The ALTER TABLE statement is used to add new columns, delete existing columns or modifying the format of columns. Afaik you cannot use CASE expressions within an alter table statement. You can do it within an UPDATE statement though.
proc sql;alter table data add status char label= "Status" format=$8.;update data set status=case when missing(DeactReason) then 'Active' else 'Inactive' end;quit;You cannot use the SQL ALTER statement to insert data.
But modifying an existing dataset is not a normal analysis step. Plus is is dangerous because if something goes wrong you might destroy your original dataset.
Just create a new dataset that has the additional information you want.
With SQL using PROC SQL.
proc sql;create table new as select *, case WHEN missing(DeactReason) THEN 'Active' ELSE 'Inactive' END as STATUSfrom old;quit;Or with normal SAS code.
data new; set old;if missing(DeactReason) THEN STATUS='Active ';ELSE STATUS='Inactive';run;