使用SPSS Syntax处理Spell数据集：为各状态定义统一起止日期

方案1：R语言（dplyr + lubridate） #

核心逻辑是先分组排序，标记重叠/连续的时间段组，再计算每组的统一起止日期，最后关联回原始数据。

library(dplyr)
library(lubridate)

# 构造原始数据
df <- tibble(
  Number = c(24,24,24,24,24),
  Start_date = dmy(c("01-Jan-1975", "10-Jul-1975", "01-Jul-1981", "25-Aug-1983", "01-Jan-1983")),
  End_Date = dmy(c("26-Mar-1975", "31-Dec-1975", "03-Oct-1981", "31-Dec-1983", "31-Aug-1984")),
  State = c(1,1,11,1,1)
)

# 处理重叠时间段并生成Begin/End
result <- df %>%
  # 按编号、状态分组
  group_by(Number, State) %>%
  # 按开始日期排序
  arrange(Start_date, .by_group = TRUE) %>%
  # 标记重叠组：当前时间段开始日期 <= 上一时间段结束日期+1天则归为同一组
  mutate(group_id = cumsum(ifelse(row_number() == 1, 1, Start_date > lag(End_Date) + days(1)))) %>%
  # 按新组计算统一起止日期
  group_by(Number, State, group_id) %>%
  mutate(
    Begin = min(Start_date),
    End = max(End_Date)
  ) %>%
  ungroup() %>%
  # 转换回原日期格式
  mutate(across(c(Start_date, End_Date, Begin, End), ~format(.x, "%d-%b-%Y")))

print(result)

方案2：SAS语言 #

通过分组排序、标记重叠组，再用PROC SQL关联统一起止日期。

/* 导入并转换日期格式 */
data raw;
    input Number Start_date :date9. End_Date :date9. State;
    format Start_date End_Date date9.;
    datalines;
24 01JAN1975 26MAR1975 1
24 10JUL1975 31DEC1975 1
24 01JUL1981 03OCT1981 11
24 25AUG1983 31DEC1983 1
24 01JAN1983 31AUG1984 1
;
run;

/* 标记重叠时间段组 */
data grouped;
    set raw;
    by Number State;
    retain group_id begin_temp end_temp;
    if first.State then do;
        group_id = 1;
        begin_temp = Start_date;
        end_temp = End_Date;
    end;
    else do;
        /* 重叠或连续则合并组 */
        if Start_date <= end_temp + 1 then end_temp = max(end_temp, End_Date);
        else do;
            group_id + 1;
            begin_temp = Start_date;
            end_temp = End_Date;
        end;
    end;
run;

/* 计算每组统一起止日期并关联回原始数据 */
proc sql;
    create table result as
    select a.*, b.Begin format=date9., b.End format=date9.
    from raw a
    left join (
        select Number, State, group_id, min(begin_temp) as Begin, max(end_temp) as End
        from grouped
        group by Number, State, group_id
    ) b
    on a.Number = b.Number and a.State = b.State 
       and a.Start_date between b.Begin and b.End 
       and a.End_Date between b.Begin and b.End;
quit;

proc print data=result; run;

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