SAS报错WHEN子句结果与前序数据类型不一致问题求助

我遇到了如下SAS报错，暂未找到解决方案，恳请大家帮忙排查：

,sum(NA_FP_flg)   as dpt_buyers
,sum(NA_FP_ORDS )   as dpt_orders
,sum(NA_FP_VISITS) as dpt_visits
,sum(NA_FP_QTY )    as dpt_QTY
,sum(NA_FP_AMT )    as dpt_SALES

,calculated dpt_SALES   /calculated dpt_buyers  as dpt_AVC
,calculated dpt_SALES  /calculated dpt_orders  as dpt_AVT
,calculated dpt_SALES   /calculated dpt_QTY     as dpt_AUR
,calculated dpt_QTY     /calculated dpt_buyers  as dpt_UPC
,calculated dpt_visits  /calculated dpt_buyers  as dpt_UPV
,sum(NA_SUB_703)                                              as NA_SUB_703
,calculated NA_SUB_703 /calculated Dept_buyers                as NA_SUB_703p
,sum(case when NA_SUB_703=1 then NA_SUB_AMNT703 else 0 end ) as NA_SUB_703_AMT

,sum(NA_SUB_715)                                              as NA_SUB_715
,calculated NA_SUB_715 /calculated Dept_buyers                as NA_sub_715p
,sum(case when NA_SUB_715=1 then NA_SUB_AMNT715 else 0 end ) as _SUB_715_AMT

,sum(NA_SUB_721)                                              as NA_SUB_721
,calculated NA_sub_721 /calculated Dept_buyers                as NA_sub_721p
,sum(case when NA_SUB_721=1 then NA_SUB_AMNT721 else 0 end ) as NA_SUB_721_AMT

我已在Stack Overflow上检索到同类问题，但我的场景有所不同：所有参与计算的字段均为数值型，我想了解报错的原因，是否是除法运算导致的问题。以下是我的程序片段：

select 
        t.customer_id 
        ,'TOTAL ch' as ch
        ,'Total bnd' as bnd
        ,'TOTAL Dpt '                    as dpt
        ,min(b.mn_p_dt)            as mn_p_dt
    
        ,max(case when t.unit in (1,2,3) and t.credit=1            and b.mn_p_dt =T.t_date then 1 else 0 end)              as dp_FP_FLAG 
        ,count(distinct case when t.unit in (1,2,3) and t.credit=1  and b.mn_p_dt =T.t_date then t.order_id end)            as dp_FP_ORDS 
        ,sum(case when t.unit in (1,2,3) and t.credit=1  and b.mn_p_dt =T.t_date  then t.net_amount else 0 end)   as dp_FP_AMT 
        ,sum(case when t.unit in (1,2,3) and t.credit=1  and b.mn_p_dt =T.t_date  then t.net_quantity else 0 end) as dp_FP_QTY 
        ,count(distinct case when t.unit in (1,2,3) and t.credit=1  and b.mn_p_dt =T.t_date  then t.t_date end)    as dp_FP_VISITS 
        ,max(case when t.unit in (1,2,3) and t.credit=1 and t.t_date between b.mn_p_dt and b.mn_p_dt +180      then 1 else 0 end) as dp_RET_TO_NA       
        ,max( nvl(sub.NA_SUB_333,0)) AS NA_SUB_333
        ,max( nvl(sub.NA_SUB_334,0)) AS NA_SUB_334
        ,max( nvl(sub.NA_SUB_335,0)) AS NA_SUB_335
        ,max( nvl(sub.NA_SUB_336,0)) AS NA_SUB_336
        ,max(nvl(sub.NA_SUB_AMNT333,0)) AS NA_SUB_AMNT333
        ,max(nvl(sub.NA_SUB_AMNT334,0)) AS NA_SUB_AMNT334
        ,max(nvl(sub.NA_SUB_AMNT335,0)) AS NA_SUB_AMNT335
        ,max(nvl(sub.NA_SUB_AMNT336,0)) AS NA_SUB_AMNT336

 from table.transaction t 
join table.customer C on (t.c_id=c.c_id and not regexp_like(nvl(c.suppress_reasons,'*'),'[EF]') )
left join (select code as pl_3,max(description) as PL3_name from table.code_list where code_id=22 group by code) pl3 on (pl3.pl_3=t.pl_3)
join  &Vousr..POP b on (t.c_id=b.c_id)  
   
 left join  (select t2.c_id        
        ,max(case when t2.unit in (1,2,3) and t2.credit=1  and trunc(t2.pl_3/100)=333 then 1 else 0 end)  as NA_SUB_333
        ,max(case when t2.unit in (1,2,3) and t2.credit=1  and trunc(t2.pl_3/100)=334 then 1 else 0 end)  as NA_SUB_334
        ,max(case when t2.unit in (1,2,3)and t2.credit=1  and trunc(t2.pl_3/100)=335 then 1 else 0 end)  as NA_SUB_335
        ,max(case when t2.unit in (1,2,3)and t2.credit=1 and  trunc(t2.pl_3/100)=336 then 1 else 0 end)  as NA_SUB_336
        ,SUM(case when t2.unit in (1,2,3) and t2.credit=1  and trunc(t2.pl_3/100)=333 then t2.nt_amt else 0 end)  as NA_SUB_AMNT333
        ,SUM(case when t2.unit in (1,2,3) and t2.credit=1  and trunc(t2.pl_3/100)=334 then t2.nt_amt else 0 end)  as NA_SUB_AMNT334
        ,SUM(case when t2.unit in (1,2,3)and t2.credit=1  and trunc(t2.pl_3/100)=335 then t2.nt_amt else 0 end)  as NA_SUB_AMNT335
        ,SUM(case when t2.unit in (1,2,3)and t2.credit=1 and  trunc(t2.pl_3/100)=336 then t2.nt_amt else 0 end)  as NA_SUB_AMNT336
        from table.transaction t2
        join  &Vousr..Loyalty_POP b2 on (t2.c_id=b2.c_id)
        where t2.unit in (1,2,3)
             and t2.credit in (1,2) 
             and t2.t_date between &VTF_RANGE.
             and t2.t_date > b2.mn_p_dt and t2.t_date < b2.mn_p_dt+180
        group by t2.c_id  ) sub on   (sub.c_id=t.c_id)
        
where t.unit in (1,2,3)
     and t.credit in (1,2) 
     and t.t_date between &VTF_RANGE.
     and t.t_date=b.mn_p_dt
    group by t.customer_id
having max(case when t.credit=1  then 1 else 0 end)=1;


%let  Vrptfpdptsql=
,sum(NA_FP_FLAG)   as Dept_buyers
,sum(NA_FP_ORDS )   as DEPT_orders
,sum(NA_FP_VISITS) as DEPT_visits
,sum(NA_FP_QTY )    as DEPT_QTY
,sum(NA_FP_AMT )    as DEPT_SALES

,calculated DEPT_SALES   /calculated Dept_buyers  as DEPT_AVC
,calculated DEPT_SALES  /calculated DEPT_orders  as DEPT_AVT
,calculated DEPT_SALES   /calculated DEPT_QTY     as DEPT_AUR
,calculated DEPT_QTY     /calculated Dept_buyers  as DEPT_UPC
,calculated DEPT_visits  /calculated Dept_buyers  as DEPT_UPV
,sum(NA_SUB_333)                                              as NA_SUB_333
,calculated NA_SUB_333 /calculated dpt_buyers                as NA_SUB_333p
,sum(case when NA_SUB_333=1 then NA_SUBAMNT333 else 0 end ) as NA_SUB_333_AMT

,sum(NA_SUB_334)                                              as NA_SUB_334
,calculated NA_SUB_334 /calculated dpt_buyers                as NA_SUB_334p
,sum(case when NA_SUB_334=1 then NA_SUBAMNT334 else 0 end ) as NA_SUB_334_AMT

,sum(NA_SUB_335)                                              as NA_SUB_335
,calculated NA_SUB_335 /calculated dpt_buyers                as NA_SUB_335p
,sum(case when NA_SUB_335=1 then NA_SUBAMNT335 else 0 end ) as NA_SUB_335_AMT

,sum(NA_SUB_336)                                              as NA_SUB_336
,calculated NA_SUB_336/calculated dpt_buyers                as NA_SUB_336p
,sum(case when NA_SUB_336=1 then NA_SUBAMNT336 else 0 end ) as NA_SUB_336_AMT;


proc sql;
create table polo.rpt_tbl1_prod as (
    select
    ch
    ,bnd
    ,dpt
    &Vrptfpdptsql.
    from polo.fpdpt_st
    where NA_FP_FLAG=1
    group by ch,bnd,dpt
union all

    select
    'Total NA' as chn
    ,'Total bnd' as bnd
    ,'TOTAL Dpt' as     dpt
    &Vrptfpdptsql.
    from polo.fpdpt_st
    where NA_FP_FLAG=1
    group by ch,bnd,dpt);
quit;

触发的报错信息如下：

ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The SUM summary function requires a numeric argument.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The SUM summary function requires a numeric argument.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: The SUM summary function requires a numeric argument.
ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.

报错原因及解决方案 #

报错核心是CASE WHEN分支返回值类型不匹配，导致后续SUM函数无法识别为数值参数，和除法运算无关，具体排查点如下：

• 字段名拼写错误导致类型隐式转换
  代码中CASE WHEN NA_SUB_XXX=1 THEN NA_SUBAMNTXXX ELSE 0 END里的金额字段名和上游子查询输出的NA_SUB_AMNTXXX少了下划线：上游输出的是NA_SUB_AMNT333这类带下划线的字段名，后续计算时写的NA_SUBAMNT333少了中间的下划线。如果上游表polo.fpdpt_st里不存在拼写错误的数值字段，SAS会默认返回字符型缺失值，导致CASE WHEN的两个分支分别返回字符型和数值型，触发类型不匹配报错，后续SUM函数拿到字符型参数就会抛出需要数值参数的错误。
• 子查询语法遗漏需要修正
  第一个SELECT语句中，LEFT JOIN的子查询开头漏写了SELECT关键字，虽然不是本次报错的直接原因，但会导致子查询输出字段类型被误判，建议补上。
• 可通过显式类型声明避免隐式转换问题
  修复字段名后，可以把CASE WHEN的ELSE分支值显式声明为和金额字段一致的数值类型，比如ELSE 0.0，进一步避免隐式类型转换带来的问题。

内容的提问来源于stack exchange，提问作者rakesh.data