Desktop productivity for business analysts and programmers

Result of WHEN clause 2 is not the same data type

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Result of WHEN clause 2 is not the same data type

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.


Accepted Solutions
Solution
3 weeks ago
Valued Guide
Posts: 558

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

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 

View solution in original post


All Replies
Solution
3 weeks ago
Valued Guide
Posts: 558

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

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 
Occasional Contributor
Posts: 9

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

Posted in reply to ChrisBrooks

Thanks Chris!  This was very helpful

Super User
Posts: 9,560

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

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 464 views
  • 2 likes
  • 3 in conversation