Desktop productivity for business analysts and programmers

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

Reply
Occasional Contributor
Posts: 12

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

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?

 

Super User
Super User
Posts: 7,720

Re: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

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","");
Occasional Contributor
Posts: 12

Re: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

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.

Super User
Super User
Posts: 7,720

Re: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

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. 

PROC Star
Posts: 1,146

Re: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

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

Occasional Contributor
Posts: 6

Re: ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

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

Ask a Question
Discussion stats
  • 5 replies
  • 2473 views
  • 2 likes
  • 4 in conversation