如何在SAS中基于Years=0的计数值计算对应占比？

数据样本 #

Cohort  Years   Count   Status
2021    2       1       Graduated
2021    1       1       Active
2021    0       1       Active
2022    1       1       Graduated
2022    0       1       Active
2017    1       1       Active
2017    6       1       Graduated
2017    5       1       Graduated
2017    4       1       Active
2017    3       1       Active
2017    2       1       Active
2017    0       1       Active
2018    0       1       Active
2018    4       1       Graduated
2018    5       1       Graduated
2018    3       1       Active
2018    1       1       Active
2018    2       1       Active
2021    2       1       Graduated
2021    1       1       Active
2021    0       1       Active
2022    1       1       Withdraw
2022    0       1       Active
2022    1       1       Active
2022    0       1       Active
2019    3       1       Active
2019    2       1       Active
2019    1       1       Active
2019    0       1       Active
2019    4       1       Graduated
2021    2       1       Withdraw
2021    1       1       Active
2021    0       1       Active
2022    1       1       Withdraw
2022    0       1       Active
2017    6       1       Graduated
2017    5       1       Graduated
2017    4       1       Active
2017    3       1       Active
2017    2       1       Active
2017    1       1       Active
2017    0       1       Active
2023    0       1       Active
2018    5       1       Graduated
2018    4       1       Graduated
2018    3       1       Active
2018    2       1       Active
2018    1       1       Active
2018    0       1       Active
2022    1       1       Active
2022    0       1       Active
2021    1       1       Active
2021    0       1       Active
2021    2       1       Withdraw
2020    0       1       Active
2020    1       1       Active
2020    2       1       Active
2020    3       1       Graduated
2023    0       1       Active
2021    2       1       Withdraw
2021    1       1       Withdraw
2021    0       1       Active
2017    6       1       Graduated
2017    5       1       Graduated
2017    4       1       Active
2017    3       1       Active
2017    2       1       Active
2017    1       1       Active
2017    0       1       Active
2023    0       1       Active
2019    4       1       Graduated
2019    3       1       Active
2019    2       1       Active
2019    1       1       Active
2019    0       1       Active
2021    2       1       Active
2021    1       1       Active
2021    0       1       Active
2021    2       1       Withdraw
2021    1       1       Withdraw
2021    0       1       Active
2023    0       1       Active
2023    0       1       Active
2023    0       1       Active
2020    3       1       Graduated
2020    1       1       Active
2020    2       1       Active
2020    0       1       Active
2023    0       1       Active
2018    3       1       Active
2018    5       1       Graduated
2018    4       1       Graduated
2018    2       1       Active
2018    1       1       Active
2018    0       1       Active
2020    3       1       Graduated
2020    2       1       Graduated
2020    0       1       Active
2020    1       1       Active
2021    0       1       Active

当前使用的Proc Tabulate脚本 #

proc tabulate data=master;
where pmajr = 'AIT' and years<9; 
TITLE1 "table1 ";
class coh  years status;
table coh*(status all), (years) *(count pctn<years>="%") /printmiss misstext='0';  *norow nocol nopercent nocum;
run;

需求说明 #

当前脚本生成的是行占比，但实际需要以Years=0的计数值（示例中为53，作为100%基准）计算其他年份的占比。比如Year1计数40对应75%，Year2计数14对应26%，即按行统计，但所有年份的占比均以对应行中Years=0的数值为基数计算。

解决方案 #

要实现该需求，需先预处理数据获取基准值，再计算占比后用Proc Tabulate展示：

步骤1：提取基准值 #

/* 获取每个coh+status分组下Years=0的计数总和作为基准 */
proc sql;
create table master_with_base as
select 
    m.*,
    base.base_count
from master m
left join (
    select coh, status, sum(count) as base_count
    from master
    where pmajr = 'AIT' and years=0
    group by coh, status
) base
on m.coh = base.coh and m.status = base.status
where m.pmajr = 'AIT' and m.years<9;
quit;

步骤2：计算占比 #

/* 基于基准值计算占比，避免除以0的情况 */
data master_with_pct;
set master_with_base;
if base_count > 0 then pct = (count / base_count)*100;
else pct = .;
run;

步骤3：生成目标表格 #

proc tabulate data=master_with_pct format=8.1;
TITLE1 "table1 - 以Years=0为基准的占比统计";
class coh years status;
var count pct;
table coh*(status all), 
      years*(count*f=8.0 pct*f=5.1*format='%') 
      /printmiss misstext='0';
run;

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