如何使用SAS代码高效实现跨表数据更新优化执行效率

你当前写的多列关联子查询更新逻辑性能很差：每更新1个字段就要对source.Members做一次全表扫描+关联匹配，字段越多、数据量越大，执行耗时会线性上涨，完全不适合批量定时ETL场景。
下面给3种适配SAS环境、性能远高于当前写法的实现方案，日常百万级数据量优先选第一种DATA步MERGE写法，是SAS做增量同步的原生最优方案：

方案1：SAS DATA步MERGE（性能最优，原生支持UPSERT） #

该方案单步即可同时完成「新增记录插入、存量记录更新」，底层按主键顺序匹配，仅需对两个表各做1-2次扫描，没有重复子查询开销。
注意使用前需要将两个表按主键IDNo排序（如果源表是数据库表，提取时直接加order by即可，无需落地后二次排序）：

/* 1. 预处理源表：提取需要的字段、重命名、过滤无效ID */
data src_prep;
    set source.Members(keep=member_name ic_no status Address State_Code Country Mobile_No
                       rename=(member_name=Name ic_no=IDNo State_Code=StateCode Mobile_No=MobileNo));
    where not missing(IDNo);
run;

/* 2. 按主键排序，表已建索引且有序可跳过该步骤 */
proc sort data=src_prep; by IDNo; run;
proc sort data=mydb.MembersProfile out=tgt_prep; by IDNo; run;

/* 3. MERGE一步完成存量更新+新增插入 */
data mydb.MembersProfile;
    merge tgt_prep(in=tgt) src_prep(in=src);
    by IDNo;
    /* 两边匹配到的存量记录，会自动用源表字段值覆盖目标表旧值 */
    /* 仅源表存在、目标表不存在的记录，会直接作为新行插入 */
    if src; 
    /* 若需要保留源表已删除的目标端历史记录，删掉上面的if src; 改为if tgt or src; 即可 */
run;

方案2：PROC SQL单连接更新（适配SQL写作习惯） #

如果更习惯写SQL逻辑，不要为每个字段单独写关联子查询，SAS的PROC SQL支持一次关联源表完成所有字段赋值，仅需做一次关联匹配，开销远低于多子查询写法：

proc sql;
/* 新增逻辑和原有逻辑一致，注意修正表名匹配问题 */
create table TmpProfile as
select src.member_name as Name
      ,src.ic_no as IDNo
      ,src.status as Status
      ,src.Address as Address
      ,src.State_Code as StateCode
      ,src.Country as Country
      ,src.Mobile_No as MobileNo
from source.Members src
where src.ic_no not in (select IDNo from mydb.MembersProfile)
  and not missing(src.ic_no);

insert into mydb.MembersProfile
select * from TmpProfile;

/* 单次关联完成所有字段更新，无重复子查询 */
update mydb.MembersProfile tgt
set (status, Address, StateCode, Country, MobileNo) = 
    (select src.status, src.Address, src.State_Code, src.Country, src.Mobile_No
     from source.Members src
     where tgt.IDNo = src.ic_no)
where exists (select 1 from source.Members src where tgt.IDNo = src.ic_no);
quit;

注意必须加最后面的where exists条件，否则源表中已删除的ID对应的目标记录，会被更新为空值。

方案3：哈希对象匹配（适配千万级以上大表场景） #

如果表数据量在千万级以上，全表排序开销过高，可以用SAS哈希对象做内存匹配，无需提前排序，匹配速度更快：

/* 先做源表预处理，和方案1的src_prep逻辑一致 */
data src_prep;
    set source.Members(keep=member_name ic_no status Address State_Code Country Mobile_No
                       rename=(member_name=Name ic_no=IDNo State_Code=StateCode Mobile_No=MobileNo));
    where not missing(IDNo);
run;

data mydb.MembersProfile new_records;
    set mydb.MembersProfile nobs=nobs;
    /* 初始化哈希对象，将源表数据加载到内存 */
    if _n_=1 then do;
        declare hash src(dataset:'src_prep');
        src.definekey('IDNo');
        src.definedata(all:'yes');
        src.definedone();
        call missing(of _all_);
    end;
    /* 匹配到源表数据则覆盖更新，匹配不到保留原值 */
    output mydb.MembersProfile;
    rc = src.replace();

    /* 遍历完目标表后，输出源表中所有未匹配到的新增记录 */
    if _n_ = nobs then do;
        src.output(dataset:'new_records');
    end;
run;
/* 将新增记录追加到目标表 */
proc append base=mydb.MembersProfile data=new_records force; run;

额外优化提示 #

• 不管用哪种方案，一定要给主键IDNo在源表、目标表都建索引，关联、排序、匹配的速度会有数量级提升
• 每日同步不要全量扫描源表，尽量加更新时间过滤条件，只抽取上次同步时间之后新增/修改过的源表记录，处理数据量越小执行速度越快
• 如果目标表是外部数据库（如MySQL、Oracle），输出时加bulkload=yes选项，调用数据库批量加载接口写入，比逐行插入速度高1-2个数量级

内容的提问来源于stack exchange，提问作者Ho Wai Loon