05-05-2016 04:32 AM
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?
05-05-2016 04:58 AM
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:
05-05-2016 05:31 AM
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 = '')
It seems that it was looking for a numeric instead of a character.
05-05-2016 05:42 AM
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.
05-05-2016 08:21 AM
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)
Try restructuring your query along these lines, and see if it works.
05-05-2016 10:50 AM
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