BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mdavidson
Quartz | Level 8
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?
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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
Quartz | Level 8
Cynthia,

I'm using the GUI approach, I will try and mess with the SAS code to get this to work...
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 199506 views
  • 4 likes
  • 2 in conversation