使用SAS基于参保数据核验住院理赔赔付资格的技术咨询

完全可以通过SAS SQL实现该需求，整体方案分为2个核心步骤：

步骤1：将宽格式enr表转为长格式 #

解决月份字段和日期格式不匹配的问题，将原表中jan-dec的12个月份列转为逐行的月份维度：

proc sql;
create table enr_long as
select id, year, 1 as month, jan as enr_status from enr
union all
select id, year, 2 as month, feb as enr_status from enr
union all
select id, year, 3 as month, mar as enr_status from enr
union all
select id, year, 4 as month, apr as enr_status from enr
union all
select id, year, 5 as month, may as enr_status from enr
union all
select id, year, 6 as month, jun as enr_status from enr
union all
select id, year, 7 as month, jul as enr_status from enr
union all
select id, year, 8 as month, aug as enr_status from enr
union all
select id, year, 9 as month, sep as enr_status from enr
union all
select id, year, 10 as month, oct as enr_status from enr
union all
select id, year, 11 as month, nov as enr_status from enr
union all
select id, year, 12 as month, dec as enr_status from enr
order by id, year, month;
quit;

步骤2：关联理赔表完成赔付规则校验 #

先计算每条理赔记录的核验时间窗口（入院前4个月月初至入院后4个月月末），再关联长格式参保表判断窗口内所有月份的参保状态是否全部为1：

proc sql;
create table clms_pay_judge as
select 
    a.*,
    case when min(b.enr_status) = 1 then '符合赔付' else '不符合赔付' end as pay_result length=20
from 
    /* 提取理赔记录并计算核验时间窗口 */
    (select 
        *,
        intnx('month', admit_dt, -4, 'b') as window_start format=yymmdd10.,
        intnx('month', admit_dt, 4, 'e') as window_end format=yymmdd10.
     from clms) a
left join 
    enr_long b
on 
    a.id = b.id
    /* 将参保的年月份转为日期格式和窗口匹配 */
    and mdy(b.month, 1, b.year) between a.window_start and a.window_end
group by 
    a.id, a.admit_dt, a.dischrg_dt, a.code, a.dr_id, a.cost, a.window_start, a.window_end;
quit;

校验结果说明 #

基于你提供的测试数据，最终判断结果如下：

• id=1 2019/02/01入院：窗口覆盖2018年10月-2019年6月，所有月份参保状态为1，符合赔付
• id=1 2019/06/01入院：窗口覆盖2019年2月-2019年10月，所有月份参保状态为1，符合赔付
• id=2 2018/10/18入院：窗口覆盖2018年6月-2019年2月，所有月份参保状态为1，符合赔付
• id=2 2019/05/18入院：窗口覆盖2019年1月-2019年9月，2019年7-9月参保状态为0，不符合赔付

如果你需要更高性能的实现，也可以不用显式生成宽转长的中间表，直接在关联逻辑中通过vvaluex函数动态取对应月份的参保状态值，不过可读性会低于上述方案。

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