BookmarkSubscribeRSS Feed
Froebel
Fluorite | Level 6

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

5 REPLIES 5
Froebel
Fluorite | Level 6
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 )
Tom
Super User Tom
Super User

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;

 

Patrick
Opal | Level 21

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

SAS Enterprise Guide vs. SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2104 views
  • 1 like
  • 4 in conversation