求助编写SAS代码：构建员工年月维度工作经历统计数据集

需求说明 #

现有数据集包含以下变量：

• directorid：员工ID
• companyid：任职公司ID
• datestartrole：任职起始日期
• dateendrole：任职结束日期

注意事项：员工可能同时从事多份工作，也可能离职后再次入职同一公司。

输入数据示例 #

Obs directorid companyid datestartrole dateendrole 
1 16 15486 1983-01-01 1993-10-01 
2 16 15486 1993-10-01 1999-02-18 
3 16 15486 1999-02-18 1999-07-22 
4 16 15486 1999-07-22 2002-01-24 
5 16 21616 2004-12-08 2007-02-23 
6 16 1623243 2010-02-03 2014-06-03 
7 16 2992276 2019-02-07 2019-09-04 
8 16 2992276 2019-09-04 2020-05-13 
9 18 26875 1989-02-01 2004-03-27 
10 27 5251 1991-01-01 1995-12-31 

目标数据集格式 #

需要生成包含以下变量的数据集，时间范围为1990年1月至2023年3月：

• directorid：员工ID
• month：月份
• year：年份
• number_of_companies：截至当月员工任职过的不同公司总数
• months_of_experience：截至当月累计工作月数

Obs directorid month year number_of_companies months_of_experience

SAS代码实现步骤 #

1. 生成统计时间范围的月度数据集 #

创建1990年1月到2023年3月的所有年月组合，作为统计的时间维度：

/* 生成1990-2023年3月的月度时间序列 */
data date_range;
  do year = 1990 to 2023;
    /* 控制每年的起始和结束月份 */
    start_month = ifn(year=1990, 1, 1);
    end_month = ifn(year=2023, 3, 12);
    
    do month = start_month to end_month;
      output;
    end;
  end;
  keep year month;
run;

2. 拆分任职记录到月度维度 #

将原始任职数据按月份拆分，计算每条任职记录在对应月份的实际工作月数（按当月天数占比计算）：

/* 替换为你的原始数据集名称 */
data role_months;
  set original_data;
  format datestartrole dateendrole date9.;
  
  /* 过滤统计范围外的日期 */
  actual_start = max(datestartrole, mdy(1, 1, 1990));
  actual_end = min(dateendrole, mdy(3, 31, 2023));
  
  /* 跳过完全不在统计范围内的记录 */
  if actual_start > actual_end then delete;
  
  /* 遍历当前任职记录覆盖的所有月份 */
  current_month = intnx('month', actual_start, 0, 'b');
  do while(current_month <= actual_end);
    month_end = intnx('month', current_month, 0, 'e');
    
    /* 计算当月工作天数占比，转换为月数 */
    work_days = min(month_end, actual_end) - max(current_month, actual_start) + 1;
    total_days = day(month_end);
    month_experience = work_days / total_days;
    
    year = year(current_month);
    month = month(current_month);
    
    output;
    current_month = intnx('month', current_month, 1, 'b');
  end;
  
  keep directorid companyid year month month_experience;
run;

3. 计算员工每月的累计指标 #

3.1 标记员工首次任职公司的时间

统计每个员工首次入职各公司的年月，用于后续累计公司数量：

proc sql;
  create table first_company_entry as
  select directorid, companyid,
         min(year) as first_year,
         min(month) as first_month
  from role_months
  group by directorid, companyid;
quit;

3.2 生成所有员工-年月组合

关联员工列表和时间序列，确保每个员工在统计范围内的每个年月都有记录：

proc sql;
  create table all_employee_time as
  select distinct o.directorid, d.year, d.month
  from original_data o, date_range d;
quit;

3.3 计算累计公司数量和工作月数

通过关联数据，统计截至每个年月的累计公司数和累计工作月数：

proc sql;
  create table final_dataset as
  select a.directorid, a.month, a.year,
         /* 累计任职过的公司数：统计首次入职时间<=当前年月的公司数量 */
         (select count(distinct companyid)
          from first_company_entry b
          where b.directorid = a.directorid
            and (b.first_year < a.year or (b.first_year = a.year and b.first_month <= a.month))) as number_of_companies,
         /* 累计工作月数：求和截至当前年月的所有月度工作月数 */
         sum(c.month_experience) as months_of_experience
  from all_employee_time a
  left join role_months c
  on a.directorid = c.directorid
     and (c.year < a.year or (c.year = a.year and c.month <= a.month))
  group by a.directorid, a.year, a.month
  order by a.directorid, a.year, a.month;
quit;

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