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

[ahtinuS]

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?

[RW9]

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]

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]

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]

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]

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