如何用SAS/SQL按指定条件从数据集生成频次统计表格

需求说明 #

给定包含ID和event_year字段的数据集（同一ID可对应多条年份记录），示例数据如下：

需要生成2017-2021年的频次统计表格，统计规则为：

• 若年份x存在该ID的event_year记录，ID计入x年频次
• 若年份x无该ID的event_year记录，但x-1年存在，则ID仍计入x年频次

最终期望输出：

SAS 实现代码 #

/* 创建示例数据集（若已有数据源可跳过此步） */
data have;
    input ID event_year;
    datalines;
1   2017
1   2018
1   2019
2   2018
2   2017
;
run;

/* 提取每个ID的有效年份范围：最早出现年份 到 最晚出现年份+1 */
data id_year_range;
    set have;
    by ID;
    if first.ID then min_year = event_year;
    max_year = event_year;
    if last.ID then do;
        end_year = max_year + 1;
        output;
    end;
    retain min_year;
run;

/* 生成目标统计年份序列（2017-2021） */
data target_years;
    do Year = 2017 to 2021;
        output;
    end;
run;

/* 关联匹配并统计各年份符合条件的ID数量 */
proc sql;
    create table want as
        select t.Year, count(distinct i.ID) as frequency
        from target_years t
        left join id_year_range i
            on t.Year between i.min_year and i.end_year
        group by t.Year
        order by t.Year;
quit;

/* 查看最终结果 */
proc print data=want noobs;
run;

SQL 实现代码（兼容SAS PROC SQL及多数关系型数据库） #

/* 创建示例表（若已有表可跳过此步） */
create table have (
    ID integer,
    event_year integer
);

insert into have values
(1, 2017),
(1, 2018),
(1, 2019),
(2, 2018),
(2, 2017);

/* 生成目标年份 + 计算各年份频次 */
with target_years as (
    select 2017 as Year union all
    select 2018 union all
    select 2019 union all
    select 2020 union all
    select 2021
),
id_year_ranges as (
    select 
        ID,
        min(event_year) as min_year,
        max(event_year) + 1 as end_year
    from have
    group by ID
)
select 
    t.Year,
    count(distinct r.ID) as frequency
from target_years t
left join id_year_ranges r
    on t.Year between r.min_year and r.end_year
group by t.Year
order by t.Year;

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