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

[ratnaparakhee]

Hi All

My below query is resulting in error "Result of WHEN clause 2 is not the same data type as the preceding results."

case
when adjud_dt ='' then paid_dt
else adjud_dt
end as new_adjud_dt format = date9.

adjud_dt and paid_dt both are formatted as date9. in the data on which i am running this code.

Could anyone please advise how to resolve this issue.

[Kurt_Bremser]

Maxim 3: Know Your Data. Run a PROC CONTENTS to see the types of the variables paid_dt and adjud_dt.

Since you (successfully, as it seems) compare adjud_dt with an empty string, it can't be a SAS date (which is numeric with a date format assigned).

[ratnaparakhee]

Hi @Kurt_Bremser 

Thanks for looking into my issue.

I did check the type of my fields, both are format date 9. and type Num. please see below-

Apart from using case when ADJUD_DT = '' , i also tried  

case when ADJUD_DT is null and

case when ADJUD_DT is missing and both are giving the same error.

I also wanted to add that i am running this query on 2 data sets. Source of one is Netezza and second is oracle.

This runs fine on data extracted on Netezza but errors out for data extracted from oracle.

Does this give you any additional insights that might help resolving this issue?

[PaigeMiller]

---

@ratnaparakhee wrote:

Hi @Kurt_Bremser 

Thanks for looking into my issue.

I did check the type of my fields, both are format date 9. and type Num. please see below-

---

You only showed one variable. What about PAID_DT?

Also, as stated above, you can't test to see if a numeric variable ADJUD_DT is equal to a character string. 

[ratnaparakhee]

Hi @PaigeMiller the second variable Paid_dt is also same type and format. 

Thanks

[PaigeMiller]

So then @Kurt_Bremser has correctly pointed to the problem, you can't test to see if a numeric variable ADJUD_DT is equal to a character string.

[ratnaparakhee]

Hi @PaigeMiller 

In that case could you please advise what should i compare it against.

I am new to SAS. In addition to '' i also cehck with 

When adjud_dt is missing  and 

when adjud_dt is null

Both resulted in the same error.

Thanks

Bhawna

[PaigeMiller]

You can use the MISSING function, which works on both numeric and character variables

case when missing(adjud_dt) then paid_dt

[Kurt_Bremser]

If you had a numeric variable and tried to compare it with an empty string, SAS would issue

ERROR: Expression using equals (=) has components that are of different data types.
 

Reference code:

proc sql;
select
  case when age = ""
    then weight
    else age
  end as new
from sashelp.class;
quit;

Since you did not get that, the variable IS character, period.

Run the PROC CONTENTS on all datasets involved in the query; it may be that you have variables with the same name in more than one dataset.

[Sajid01 · Accepted solution]

The problem with your code is the statement when adjud_dt ='' then paid_dt.

Date being a numeric one must use adjud_dt = .

This is because missing numeric values are represented by a dot or period and not as used in the code.
The two single quotes as used in the code posted indicate a missing character value. That is what the error message is indicating. I am giving the sample code below which works correctly.

data old;
informat adjud_dt  paid_dt date9.;
format adjud_dt  paid_dt date9.;
input adjud_dt  paid_dt;
datalines;
23DEC2021 22DEC2021
20DEC2021 21DEC2021
 .                  19DEC2021
 ;
 run;
proc sql;
create table new as
select *,
case
when adjud_dt=.  then paid_dt
else adjud_dt
end as new_adjud_dt format date9. 
from old;
quit;

one can also use missing function as shown by @PaigeMiller , in that case the sql statement would be as follows

proc sql;
create table new as
select *,
case
when missing(adjud_dt)  then paid_dt
else adjud_dt
end as new_adjud_dt format date9. 
from old;
quit;

Either way the result is same.