基于ID与Knee匹配两个SAS数据集并合并数据的技术求助

现有两个SAS数据集，id与knee共同标识唯一记录，vx代表不同访视的变量（示例中x取0、1、2，实际数据含数千条记录及数百变量）。需求是创建一个新数据集，匹配id和knee，包含两个数据集的对应数据，并添加inds1（标记来自ds1）和inds2（标记来自ds2）字段。

原始数据集代码 #

/* 数据集ds1 */
data ds1;
    input id knee $ v0acl v0pcl v2acl v2pcl;
    cards;
1 R 0 1 1 0
2 L 1 1 0 0
2 R 1 1 1 1
3 L 0 0 0 0 
;
run;

proc print data=ds1; run;

/* 数据集ds2 */
data ds2;
    infile datalines missover;
    input @1 id @3 knee $ @5 v0acl @7 v0pcl @9 v1acl @11 v1pcl @13 v2acl @15 v2pcl;
    datalines;
1 R 0 0 1 0 1 1
2 L 0 1 0 0 1 0
2 R 1 1 1 1 . .
3 L 0 0 . . 0 0
4 R 1 1 1 1
;
run;

期望输出 #

id   knee  v0acl   v0pcl   v1acl   v1pcl   v2acl   v2pcl inds1 inds2
 1    R     0       1                       1       0     1     0
 1    R     0       0       1       0       1       1     0     1
 2    L     1       1                       0       0     1     0
 2    L     0       1       0       0       1       0     0     1
 2    R     1       1                       1       1     1     0
 2    R     1       1       1       1                     0     1
 3    L     0       0                       0       0     1     0
 3    L     0       0                               0     0     0 

尝试过程及错误 #

尝试用PROC SQL分四步实现，步骤1成功获取共同的id/knee组合，但步骤2执行报错：

步骤1：获取共同id/knee组合 #

proc sql;
    create table want as
    select a.id, a.knee
    from ds1 as a
    inner join ds2 as b
    on a.id = b.id
    and a.knee = b.knee;
quit;

步骤2：筛选ds1中属于共同id/knee的记录（报错） #

proc sql;
    create table want2 as
    select b.*
    from ds1 as b
    where b.id and b.knee in (select a.id, a.knee from ds2 as a);
    order by id, knee;
quit;

报错信息：

A subquery cannot select more than one column.

后续计划筛选ds2中匹配的记录并合并，但需先解决步骤2的错误。

解决方案 #

1. 修正步骤2的错误 #

IN子查询仅支持返回单列，无法匹配多列（id+knee），可改用以下两种方法：

方法A：用EXISTS关联子查询

直接关联匹配id和knee，同时添加来源标记：

proc sql;
    create table want2 as
    select b.*, 1 as inds1, 0 as inds2
    from ds1 as b
    where exists (
        select 1 from ds2 as a
        where a.id = b.id and a.knee = b.knee
    )
    order by id, knee;
quit;

方法B：用步骤1的want表关联筛选

基于已生成的共同id/knee表进行匹配：

proc sql;
    create table want2 as
    select b.*, 1 as inds1, 0 as inds2
    from ds1 as b
    inner join want as w
    on b.id = w.id and b.knee = w.knee
    order by id, knee;
quit;

2. 筛选ds2中匹配的记录 #

用同样逻辑处理ds2，添加对应来源标记：

proc sql;
    create table want3 as
    select b.*, 0 as inds1, 1 as inds2
    from ds2 as b
    where exists (
        select 1 from ds1 as a
        where a.id = b.id and a.knee = b.knee
    )
    order by id, knee;
quit;

3. 合并两个结果集 #

用UNION ALL合并两个数据集，自动对齐缺失变量（如ds1缺少的v1acl/v1pcl会补为缺失值）：

proc sql;
    create table final_want as
    select * from want2
    union all
    select * from want3
    order by id, knee, inds1 desc;
quit;

一站式高效方案 #

可直接通过PROC SQL的UNION ALL完成所有逻辑，避免多步骤：

proc sql;
    create table final_want as
    /* 保留ds1中匹配的记录，标记来源 */
    select a.id, a.knee, a.v0acl, a.v0pcl, . as v1acl, . as v1pcl, a.v2acl, a.v2pcl, 1 as inds1, 0 as inds2
    from ds1 as a
    where exists (select 1 from ds2 as b where a.id=b.id and a.knee=b.knee)
    union all
    /* 保留ds2中匹配的记录，标记来源 */
    select b.id, b.knee, b.v0acl, b.v0pcl, b.v1acl, b.v1pcl, b.v2acl, b.v2pcl, 0 as inds1, 1 as inds2
    from ds2 as b
    where exists (select 1 from ds1 as a where a.id=b.id and a.knee=b.knee)
    order by id, knee, inds1 desc;
quit;

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