topic CASE WHEN Statement in SAS Enterprise Guide

CASE WHEN Statement

mdavidson — Wed, 09 Jul 2008 13:53:34 GMT

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?

Re: CASE WHEN Statement

Cynthia_sas — Thu, 27 Jul 2017 16:48:00 GMT

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;

Re: CASE WHEN Statement

mdavidson — Wed, 09 Jul 2008 16:51:45 GMT

Cynthia,

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

Re: CASE WHEN Statement

Cynthia_sas — Wed, 09 Jul 2008 17:14:17 GMT

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