topic Re: Result of WHEN clause 2 is not the same data type in SAS Enterprise Guide

Result of WHEN clause 2 is not the same data type

Bankshot — Thu, 25 Jan 2018 23:50:24 GMT

Hi,

I received this error when I tried to do a count distinct with conditions in proc sql:

proc sql;
create table cnt_SOR as
select
default_dt_yyyymm,
count(distinct (case when app_sys_no = '337' then obligor_no else 0 end)) as Cnt337,
count(distinct (case when app_sys_no = '339' then obligor_no else 0 end)) as Cnt339,
count(distinct (case when app_sys_no = '342' then obligor_no else 0 end)) as Cnt342,
count(distinct (case when app_sys_no = '362' then obligor_no else 0 end)) as Cnt362
from auto_LGD
where default_dt_yyyymm >= 201501 and app_sys_no in ('337', '339', '342', '362')
group by 1 order by 1;
quit;

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

Could someone help me on what's going on? app_sys_no is character format, and I don't see other data type conflicts here.

Re: Result of WHEN clause 2 is not the same data type

ChrisBrooks — Fri, 26 Jan 2018 00:20:39 GMT

The problem isn't with app_sys_no - it's because obligor is, presumably, character and 0 is of course numeric.

If you change your case statements to read as follows you should be OK

count(distinct (case when app_sys_no = '337' then obligor_no else "" end)) as Cnt337, 
count(distinct (case when app_sys_no = '339' then obligor_no else "" end)) as Cnt339, 
count(distinct (case when app_sys_no = '342' then obligor_no else "" end)) as Cnt342, 
count(distinct (case when app_sys_no = '362' then obligor_no else "" end)) as Cnt362

Re: Result of WHEN clause 2 is not the same data type

Bankshot — Fri, 26 Jan 2018 01:51:21 GMT

Thanks Chris! This was very helpful

Re: Result of WHEN clause 2 is not the same data type

Kurt_Bremser — Fri, 26 Jan 2018 07:28:24 GMT

If it did solve your problem, please mark @ChrisBrooks' post as the solution.