SAS中用PROC SQL/PROC REPORT生成含全TC值的交叉频数表方案求助

现有SAS数据集如下：

data sample;
input FY TC;
datalines;
2013 1
2014 5
2013 6
2015 7
2016 1
2015 5
2016 2
2014 2
2013 7
2014 4
2017 5
2018 1
2018 6
2015 4
2014 2
2015 4
;
run;

期望输出可直接用于后续计算的数据集：

FY  tc1 tc2 tc3 tc4 tc5 tc6 tc7
2013    1   0   0   0   0   1   1
2014    0   2   0   1   1   0   0
2015    0   0   0   2   1   0   1
2016    1   1   0   0   0   0   0
2017    0   0   0   0   1   0   0
2018    1   0   0   0   0   1   0

核心要求：

• TC固定取值为1-7，即使某TC值无对应数据，交叉表也必须保留对应列，空值填充为0
• 优先使用PROC SQL或PROC REPORT实现，不可使用PROC TABULATE、PROC FREQ
• 输出结果为可直接用于后续计算的SAS数据集

方案1：PROC REPORT实现 #

先预处理生成全量FY+TC的组合框架，补全缺失项后用PROC REPORT输出，同时生成可用数据集：

/* 提前定义TC列名格式，确保列顺序为tc1-tc7 */
proc format;
    value tcname
    1='tc1'
    2='tc2'
    3='tc3'
    4='tc4'
    5='tc5'
    6='tc6'
    7='tc7';
run;

/* 生成全量FY和TC1-7的笛卡尔积，确保无缺失组合 */
proc sql noprint;
    create table all_comb as
    select distinct a.FY, b.TC
    from sample a,
         (select TC from (select 1 as TC union select 2 union select 3 union select 4 union select 5 union select 6 union select 7)) b
    order by FY, TC;

    /* 左关联原表统计频数，缺失值补0 */
    create table stat_temp as
    select a.FY, a.TC, coalesce(count(b.TC),0) as cnt
    from all_comb a
    left join sample b
    on a.FY = b.FY and a.TC = b.TC
    group by a.FY, a.TC;
quit;

/* 用PROC REPORT转置为宽表，输出结果数据集 */
proc report data=stat_temp out=want_report(drop=_BREAK_);
    columns FY TC, cnt;
    define FY / group 'FY';
    define TC / across ' ' order=data format=tcname.;
    define cnt / sum ' ';
run;

输出的want_report数据集完全符合要求，可直接用于后续计算。

方案2：PROC SQL实现 #

直接通过条件聚合生成宽表，无需额外转置步骤，代码更简洁：

proc sql;
    create table want_sql as
    select FY,
           sum(case when TC=1 then 1 else 0 end) as tc1,
           sum(case when TC=2 then 1 else 0 end) as tc2,
           sum(case when TC=3 then 1 else 0 end) as tc3,
           sum(case when TC=4 then 1 else 0 end) as tc4,
           sum(case when TC=5 then 1 else 0 end) as tc5,
           sum(case when TC=6 then 1 else 0 end) as tc6,
           sum(case when TC=7 then 1 else 0 end) as tc7
    from sample
    group by FY
    order by FY;
quit;

输出的want_sql数据集自动将无数据的TC列填充为0，完全满足需求。

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