CASE WHEN Statement

[mdavidson]

I'm trying to get the following case,when,then,else expression to work in a query:

CASE WHEN FIELD1 IS 0 THEN 'N/A' ELSE FIELD1 END

Here is the error I am getting in my log:
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Is there any way to return a character from a numeric field in a case when statement?

[Cynthia_sas · Accepted solution]

Hi:
If you're using SQL code, then your CASE statement needs an "AS" to create the "new" column. In the example below, NEWVAR is a character variable that is created based on the CASE condition that an observation meets.

cynthia

proc sql;
create table work.newclass as
select a.Name,
a.Sex,
a.Age,
(case
when a.sex = 'F' and a.age lt 13 then 'One'
when a.sex = 'F' and a.age ge 13 then 'Two'
when a.sex = 'M' and a.age lt 14 then 'Three'
when a.sex = 'M' and a.age ge 14 then 'Four'
else 'None' 
end) as newvar
from sashelp.class as a;
quit;

proc print data=newclass;
title 'SQL CASE Example ';
run;

[mdavidson]

Cynthia,

I'm using the GUI approach, I will try and mess with the SAS code to get this to work...

[Cynthia_sas]

Hi:
In the GUI window, there's a place to click for a COMPUTED column when you build the query -- it's over to the left of the Query window -- that's what you want. Then in the Expression editor, you add the WHOLE CASE expression
(CASE ... END) in the Expression editor.

Do NOT add the AS portion of the clause in the Expression Editor -- only the whole clause in parentheses. EG adds the AS from the information you put into the Computed column window for the name of the variable. Otherwise EG uses a funny name like Calculated1 or Calculation1 as the name of the new variable.

It's probably worth working with the GUI window a bit to see if you can figure it out. It sticks in my mind that there were some differences in the syntax of the CASE clause in earlier versions of EG versus EG 4.1 -- but basically I believe that in either version you add the entire CASE clause into the Expression Editor.

cynthia