如何用SAS代码按ID生成标识是否同时存在E10和E11组合的新变量

原始数据集 #

需求说明 #

需要新增名为Has the combination的字段，规则为：

• 若同一ID的VALUE字段中同时存在E10和E11，则该ID下所有行的此字段值为1
• 否则为0

预期输出 #

1. SQL实现 #

核心思路：先分组统计每个ID是否同时包含E10和E11，再将结果关联回原表。

WITH id_check AS (
    SELECT 
        ID,
        CASE 
            WHEN SUM(CASE WHEN VALUE = 'E10' THEN 1 ELSE 0 END) > 0 
                 AND SUM(CASE WHEN VALUE = 'E11' THEN 1 ELSE 0 END) > 0 
            THEN 1 
            ELSE 0 
        END AS Has_the_combination
    FROM your_table
    GROUP BY ID
)
SELECT 
    t.ID,
    t.CASE,
    t.VALUE,
    t.DUMMY,
    ic.Has_the_combination
FROM your_table t
JOIN id_check ic ON t.ID = ic.ID;

2. Python Pandas实现 #

核心思路：用groupby结合transform，一次性给每个ID的所有行标记结果。

import pandas as pd

# 假设原始数据已加载到df中
df = pd.DataFrame({
    'ID': [123,123,123,123,987,987,987],
    'CASE': [4736,1254,997,7655,1234,6376,980],
    'VALUE': ['E10','N65','E11','x','x','E10','E18'],
    'DUMMY': [1,0,1,0,0,1,0]
})

# 新增目标字段
df['Has the combination'] = df.groupby('ID')['VALUE'].transform(
    lambda x: 1 if ('E10' in x.values and 'E11' in x.values) else 0
)

print(df)

3. SAS实现 #

核心思路：按ID排序后，用RETAIN变量记录当前ID的E10/E11存在状态，再批量赋值。

proc sort data=your_data;
    by ID;
run;

data final_data;
    set your_data;
    by ID;
    retain has_e10 has_e11;
    if first.ID then do;
        has_e10 = 0;
        has_e11 = 0;
    end;
    if VALUE = 'E10' then has_e10 = 1;
    if VALUE = 'E11' then has_e11 = 1;
    Has_the_combination = (has_e10 and has_e11);
    if last.ID then do;
        do until(last.ID);
            output;
            set your_data;
            by ID;
            Has_the_combination = (has_e10 and has_e11);
        end;
        output;
    end;
    drop has_e10 has_e11;
run;

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