SAS PROC SQL按月份分组聚合出现重复记录解决方案

你遇到的同月份重复记录问题由3个核心错误导致：

• 第二段聚合查询GROUP BY字段错误：你需要按月份汇总，实际写的是按POLICY_字段分组，完全和分组目标不匹配
• 月份字段存储逻辑错误：POLICY_VINTAGE虽然设置了yymmd7.的月份格式，但实际存储的是天级别的日期值，相同显示月份的不同天数据会被判定为不同分组值
• 字段名不匹配：第一段查询生成的分类字段别名是POLISA_，第二段查询WHERE条件用的POLISA_INFORMACJA是不存在的字段，过滤逻辑完全失效

第一步：调整PolisyEnd表生成逻辑，将POLICY_VINTAGE处理为真实的月份值 #

用INTNX()函数把日期对齐到当月第一天，确保相同月份的所有数据对应同一个值：

PROC SQL;     
create table PolisyEnd as 
    select       
        t4.spr_NRB as NRB
        /* 对齐到当月第一天，存储为真正的月份级日期，保留yymmd7.格式 */
        ,intnx('month', datepart(t1.PRP_END_DATE), 0, 'b') as POLICY_VINTAGE format yymmd7.
        ,case
            when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. < 0 THEN 'W' 
            when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. >= 0 and datepart(t1.PRP_END_DATE) - &gv_date_dly. <=7 THEN 'U' 
            when datepart(t1.PRP_END_DATE) IS NOT NULL and datepart(t1.PRP_END_DATE) - &gv_date_dly. >= 30 THEN 'A' 
            when datepart(t1.PRP_END_DATE) IS NULL THEN 'NO INFO' 
        end as POLISA_INFORMACJA
    from cmz.WMDTZDP_BH t1
    left join (select distinct kontr_id,obj_oid from cmz.BH_D_ZAB_X_ALOK_&thismonth) t2 
        on t2.obj_oid = t1.obj_oid
    left join (select distinct data_danych, kontr_id, kre_nrb from dm.BH_WMDTKRE_&thismonth) t3
        on t3.kontr_id = t2.kontr_id
    left join (select distinct spr_NRB, spr_STATUS from _mart.mart_kred) t4
        on t4.spr_NRB = t3.kre_nrb 
    where datepart(t1.data_danych) between '5Aug2019'd and &gv_date_dly. 
        and t1.Actual = "T"
        and t4.spr_STATUS ="A"
    group by 
        datepart(t1.data_danych)   
        ,calculated POLICY_VINTAGE
        ,calculated POLISA_INFORMACJA
        ,NRB
;
quit;

第二步：修正聚合查询的分组逻辑，按POLICY_VINTAGE分组得到单条月度记录 #

PROC SQL;   
    create table PolisyEnd1 as
    select
        POLICY_VINTAGE
        ,count(distinct NRB) as NUMBER /* 加distinct避免同NRB重复计数，不需要可删除distinct */
    from PolisyEnd
    where POLISA_INFORMACJA ="A"
    group by POLICY_VINTAGE
;
Quit;

• 去掉不必要的distinct关键字，聚合查询本身按分组字段去重，不需要额外加distinct
• 如果是要统计唯一客户数，用count(distinct NRB)，如果是统计保单总数直接用count(*)即可

内容的提问来源于stack exchange，提问作者Przemek Dabek