Result of WHEN clause 2 is not the same data type

[Bankshot]

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.

[ChrisBrooks · Accepted solution]

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 

[Bankshot]

Thanks Chris!  This was very helpful

[Kurt_Bremser]

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