SAS PROC SQL含缺失值条件左连接的数据对账匹配问题

问题场景 #

数据对账（Reconciliation）场景下需通过PROC SQL的left join合并两张表，匹配规则如下：

• 第一匹配键为受试者IDsubj，必须完全匹配
• 第二匹配键为日期字段：若两表日期值可精确匹配，提取第二张表（表b）对应行全部字段；若日期无精确匹配，仅将b表日期字段置为缺失，仍需提取同subj下b表日期缺失行的其余字段

原有写法将两个匹配键做等值关联，SQL默认逻辑下只要日期匹配失败，b表所有字段都会返回缺失值，不符合预期。示例中subj=2、dat=10may2022的记录，预期匹配b表中subj=2、dat_new为缺失的行，返回other_var=11。

原有示例代码：

data a;
 subj=1; dat="01jan2022"d; output;
 subj=1; dat="01feb2022"d; output;
 subj=1; dat="05mar2022"d; output;
 subj=2; dat="10may2022"d; output;
 subj=2; dat="11jun2022"d; output;
run;

data b;
 subj=1; dat_new="01jan2022"d; other_var=1; output;
 subj=1; dat_new="01feb2022"d; other_var=2; output;
 subj=1; dat_new="05mar2022"d; other_var=3; output;
 subj=2; dat_new=.; other_var=11; output;
 subj=2; dat_new="11jun2022"d; other_var=21; output;
run;

/* 原有不满足需求的写法 */
proc sql;
create table ab_reconciliation
as
select a.subj, a.dat format=date9., b.dat_new format=date9., b.other_var
from a as t1
left join b as t2
on t1.subj=t2.subj
and dat=dat_new; 
quit;

实现方案 #

核心逻辑是给匹配规则加优先级：优先匹配日期完全一致的行，无精确匹配时兜底匹配同subj下日期缺失的默认行，以下两种写法均可实现需求。

方案1：调整JOIN条件加存在性判断（代码最简洁） #

直接在on子句中加入兜底匹配逻辑，通过not exists判断当前a表记录是否存在精确日期匹配，不存在时才关联b表的日期缺失行，关联后对兜底匹配的记录手动将dat_new置空即可。

proc sql;
create table ab_reconciliation
as
select t1.subj, 
       t1.dat format=date9.,
       /* 兜底匹配时dat_new置空 */
       ifn(t1.dat=t2.dat_new, t2.dat_new, .) as dat_new format=date9.,
       t2.other_var
from a as t1
left join b as t2
on t1.subj = t2.subj
and (
  /* 优先级1：精确匹配日期 */
  t1.dat = t2.dat_new
  /* 优先级2：无精确匹配时，关联同subj下dat_new缺失的行 */
  or (missing(t2.dat_new) 
      and not exists (
        select 1 from b as b_tmp 
        where b_tmp.subj = t1.subj and b_tmp.dat_new = t1.dat
      )
  )
);
quit;

方案2：标记匹配优先级后筛选（适合复杂多规则场景） #

先给b表的所有行标记匹配优先级：有明确日期值的行优先级为1（最高），日期缺失的兜底行优先级为2（最低）。关联时拉取同subj下所有可匹配的行，最后按分组取优先级最高的记录即可，逻辑更易扩展。

proc sql;
create table ab_reconciliation
as
select t1.subj,
       t1.dat format=date9.,
       ifn(t1.dat=t2.dat_new, t2.dat_new, .) as dat_new format=date9.,
       t2.other_var
from a as t1
left join (
  select *,
         /* 标记匹配优先级：有日期值的行优先级更高 */
         case when not missing(dat_new) then 1 else 2 end as match_prio
  from b
) as t2
on t1.subj = t2.subj
and (t1.dat = t2.dat_new or missing(t2.dat_new))
/* 每个a表记录只保留优先级最高的匹配结果 */
group by t1.subj, t1.dat
having min(t2.match_prio) = t2.match_prio;
quit;

运行结果 #

两种方案输出结果完全一致，符合预期：

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