BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ratnaparakhee
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Sajid01
Meteorite | Level 14

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.

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

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
Obsidian | Level 7

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-

ratnaparakhee_0-1639481533136.png

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
Diamond | Level 26

@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-

ratnaparakhee_0-1639481533136.png


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. 

--
Paige Miller
ratnaparakhee
Obsidian | Level 7

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

ratnaparakhee_0-1639482559510.png

Thanks

 

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
ratnaparakhee
Obsidian | Level 7

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
Diamond | Level 26

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

 

case when missing(adjud_dt) then paid_dt
--
Paige Miller
Kurt_Bremser
Super User

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
Meteorite | Level 14

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3713 views
  • 2 likes
  • 4 in conversation