BookmarkSubscribeRSS Feed
ahtinuS
Fluorite | Level 6

Good day,

 

I am using SAS EG 9.1.3 and cannot resolve this error: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

It is a simple case statement using the query builder:

The column CLOSE_DATE is a character and the THEN attempts to put in a blank.

CASE  WHEN (TABLE.CLOSE_DATE = '0000-00-00') THEN (CLOSE_DATE = '') ELSE TABLE.CLOSE_DATE END

If not a CASE statement, what could I use?

 

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I don't use EG, but it sounds like a variable is not being found, and looking at your example, why specify the table name each time other than the assignment (also please don't code all in upper case its very hard to read):

case  when (table.close_date = '0000-00-00') then (table.close_date = '') else table.close_date end
^ ^

As I asy, I don't use EG, so don't know about this query builder, but in normal code you could just do: 

table.close_date=tranwrd(table.close_date,"0000-00-00","");
ahtinuS
Fluorite | Level 6

Apologies for the caps and using the table name - this is the way SAS EG query builder writes the query, I have converted to lower case for ease of reading.

 

I did manage to resolve the error but it created a new problem.

I get a 0 which then converts to SAS date 1Jan1960 instead of a blank.

 

The fix was:

 

case when (table.close_date = '0000-00-00')

            then (table.close_date = '')

               else input(table.close_date,best8.)

end

 

It seems that it was looking for a numeric instead of a character.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

No probs.  So the variable that this applies to is a numeric date variable, i.e. its not the table.close_date as that is character?  Its hard to say not knowing how the query builder works as your case when seems wrong, you wouldn't then <assignment>.

In actual code a case would look like:

proc sql;
  create table WANT as
  select case when CLOSE_DATE='0000-00-00' then .
              else input(CLOSE_DATE,best8.) end as RESULT_VARIABLE
  from   TABLE;
quit;

So we are saying if close_date is the character string '0000-00-00', then the result of the clause will be missing (.).  Otherwise input the character string from close_date into a numeric result.  The as sets RESULT_VARIABLE which is a numeric variable to either . or the numeric version of close_date. 

TomKari
Onyx | Level 15

I think I see part of your problem. The CASE statement in the EG query builder is a bit of an odd fish, due to the fact that the underlying code is SQL.

 

Generally, what you should be be supplying is just the RESULT of calculation, and the SQL code will do the assignment. So if I wanted to assign values of YOUNG or OLD to a variable based on age, I would do it like this:

 

case when (age < 18)
then ('YOUNG')
else ('OLD')
end

 

Try restructuring your query along these lines, and see if it works.

Tom

thorneton
Fluorite | Level 6

The logic of your original code may be ok,  change  '' to ' '. Insert a space between the quote marks.  Quotes without a blank between is a null value which can be interpreted as a missing value. Which would explain the different data types error.  Hope this helps

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
  • 5 replies
  • 27478 views
  • 2 likes
  • 4 in conversation