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.
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.
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).
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?
@ratnaparakhee wrote:
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.
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.
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
You can use the MISSING function, which works on both numeric and character variables
case when missing(adjud_dt) then paid_dt
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.