Error: Result of when clause 8 is not same data type as the preceding results

[GN0001]

hello team,

I have a case statement, that all have character data type after then, but it gives me error message.

Regards,

blue blue

[Astounding]

I baked a cake but it tastes funny. I did use eggs. What did I do wrong?

[PaigeMiller]

Hello, @GN0001 

You have been in this forum long enough now to know that when you get an error message, we need you to show us the LOG for this PROC.

INSTRUCTIONS FOR PROVIDING THE LOG: We need the ENTIRE log for this PROC, 100% of the log for this PROC, with nothing chopped out, every single character. Copy this portion of the log as text. Click on the </> icon and paste your log as text into the window that appears.

[GN0001]

Hello team,

It is not doable for me to give you the entire log, it is my work computer. If it was my personal computer, I would have copied and send everything out.

I posted the code.

Thanks,

blue blue

[Tom]

Check carefully for unbalanced parentheses and unbalanced quotes.  Especially if you really have named your variable starting with digits so that you need to use name literals to reference them.  That means there are more chances to have messed up the quotes.

[Tom]

---

@GN0001 wrote:

hello team,

I have a case statement, that all have character data type after then, but it gives me error message.

Regards,

blue blue

---

There is either a typo you are not seeing or the variables in the source dataset are not the data type you think they are.

Please show the code, preferable the log.  Also show the definition of the input dataset(s).

[GN0001]

This is the code: This gives error and the affected code is where joins are: Thanks, blue blue

Proc sql;
create table table_out as
select distinct c.*,
case
when (not missing(25percentile) and rate *100 < 25percentile) then '<25'
when (not missing(25percentile) and rate *100 >=25percentile and rate <50percentile) then '25'
when (not missing(50percentile) and rate *100 >=50percentile and rate <75percentile) then '50'
when (not missing(75percentile) and rate *100 >=75percentile and rate <80percentile) then '75'
when(not missing (80percentile) and rate *100 >80percentile) then 'done'
else 'error'
end as thisjunk
, from lefttable c left join.....

[Tom]

That code cannot work.   You cannot have a variable whose name starts with a digit.

[GN0001]

Where are you referring?

[GN0001]

Here I put digit but in real world it starts with BQ_.

Thanks,

blue blue

[GN0001]

Can you please let me know which line? I don’t have variables with digits!
Thanks
Blue green

[GN0001]

sorry, I noticed it, in real world it starts with letters. It is QB_25, QB_50,..

Thanks,

blue greeen

[Tom · Accepted solution]

Use a simple process of elimination to discover which WHEN clause is generating the wrong type of value.

Comment out some of the lines and see if the error is eliminated or not.  Depending on the result comment out other sections until you have found the offending section (or sections).