SAS Case Statement - if this, then that . else leave as is

Question

I have a column with some unwanted data "XX" and want to set them as 0. Otherwise, keep the column's data as it is. Something similar to if columnA = "XX" then 0 Else columnA. I tried doing this using a proc SQL SAS step, but that did not work. So looking for the most efficient way of doing using SAS SQL. Thanks for any help.

if columnA = "XX" then 0 
Else columnA
End as columnA

Answer 1

This should work in proc sql:

proc sql;
    select (case when columnA = 'xx' then '0' else columnA end) as columnA
    from t;

Note that the 0 is a string in this expression. columnA appears to be a string (based on the comparison). A case expression returns a value with a specified type -- and in this case, it should be a string.

Answer 2

It's a CASE statement in SQL.

Case when columnA='XX' then '0'
     else columnA 
end as ColumnA_New

original code: Case when columnA='XX' then 0 else columnA end as ColumnA

Answer 3

Both prior solutions work and are fine. Indeed, the solution was asked in proc sql, yet I find data step-approach seems more simple. Posting it here for completeness sake.

/*Generate sample data*/
data begin; 
  input value @@; 
  cards; 
1 2  3  2.5 1.7 3 34 33 33 33.7 34 34
  ; run;

/*replace value=33 with zeros*/
data wanted; 
  set begin; 
    if value = 33 then value=0; 
run;