Hello Experts
I have a data set with about 10 columns, I want to update the column value of one column based on value of another column , how can I do that
something like this on sql but I need to update SAS dataset Cer_Users where Initiative & ord_creatr_user_id are columns
UPDATE Cer_Users
SET Initiative = CASE WHEN ord_creatr_user_id ='987' and Initiative = 'UnderGrad'
THEN 'UC-UnderGrad'
ELSE Initiative
END
Short version:
data cer_users;
set cer_users;
if ord_creatr_user_id ='987' and Initiative = 'UnderGrad'
then Initiative ='UC-UnderGrad';
run;
but you can also run your SQL code in PROC SQL;
The coloring in the SAS editor is not 100% perfect.
See the documentation of the UPDATE Statement
Looks ok to me.
But if you are unsure try it first on a COPY of the dataset. In fact I would avoid using the SQL UPDATE statement unless there is no other way to get what you want because of the risks involved in modifying an existing dataset. If it doesn't work as expected you will need to have a back up the original data.
Your UPDATE statement can be simpler (and faster and safer) if you eliminate the CASE and use an actual WHERE clause instead. That way you are only changing the observations that need changing.
UPDATE Cer_Users
SET Initiative = 'UC-UnderGrad'
WHERE ord_creatr_user_id ='987' and Initiative = 'UnderGrad'
;
Note it is normally much easier, clearer and safer to just use normal SAS code than try to figure out how to do things with SQL code.
So here is a data step to make a NEW dataset with the fixed values. That way you can always go back to the original dataset if the logic or the coding was incorrect.
data fixed_cer_users;
set cer_users;
if ord_creatr_user_id ='987' and Initiative = 'UnderGrad' then
Initiative = 'UC-UnderGrad'
;
run;
@Froebel wrote:
Hello Experts
I have a data set with about 10 columns, I want to update the column value of one column based on value of another column , how can I do that
something like this on sql but I need to update SAS dataset Cer_Users where Initiative & ord_creatr_user_id are columns
UPDATE Cer_Users
SET Initiative = CASE WHEN ord_creatr_user_id ='987' and Initiative = 'UnderGrad'
THEN 'UC-UnderGrad'
ELSE Initiative
END
Your SQL would update all rows but in the else case with the original value (=no change).
It would be more efficient it you only update the rows where the value actually needs to change using code as below.
proc sql;
UPDATE Cer_Users
SET Initiative = 'UC-UnderGrad'
WHERE ord_creatr_user_id ='987' and Initiative = 'UnderGrad'
;
quit;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.