Desktop productivity for business analysts and programmers

CASE WHEN Statement

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

CASE WHEN Statement

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?

Accepted Solutions
Solution
‎07-27-2017 12:48 PM
SAS Super FREQ
Posts: 8,814

Re: CASE WHEN Statement

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎07-27-2017 12:48 PM
SAS Super FREQ
Posts: 8,814

Re: CASE WHEN Statement

[ Edited ]

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;
Contributor
Posts: 53

Re: CASE WHEN Statement

Cynthia,

I'm using the GUI approach, I will try and mess with the SAS code to get this to work...
SAS Super FREQ
Posts: 8,814

Re: CASE WHEN Statement

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
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 9891 views
  • 0 likes
  • 2 in conversation