topic Re: I want to update the value of one column in sas data set based on value of another column in SAS Enterprise Guide

I want to update the value of one column in sas data set based on value of another column

Froebel — Thu, 01 Feb 2024 20:07:27 GMT

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

Re: I want to update the value of one column in sas data set based on value of another column

Kurt_Bremser — Thu, 01 Feb 2024 20:12:06 GMT

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;

Re: I want to update the value of one column in sas data set based on value of another column

Froebel — Thu, 01 Feb 2024 20:29:31 GMT

Thank you. I tried proc sql but the
update command under proc sql is not recognized.( The update text does not turn blue like the select text )

Re: I want to update the value of one column in sas data set based on value of another column

Kurt_Bremser — Thu, 01 Feb 2024 20:51:34 GMT

The coloring in the SAS editor is not 100% perfect.

See the documentation of the UPDATE Statement

Re: I want to update the value of one column in sas data set based on value of another column

Tom — Thu, 01 Feb 2024 23:48:57 GMT

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;

Re: I want to update the value of one column in sas data set based on value of another column

Patrick — Thu, 01 Feb 2024 23:52:54 GMT

@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;