SAS数据转置与汇总：统计月度账户状态数量及指标平均值

我有如下结构的数据集，记录了各账户编号在6-11月的状态情况，以及对应月份的指标数值：

Account Number  6m      7m      8m      9m      10m     11m     6m_Metric    7m_metric   8m_metric   9m_metric   10m_metric      11m_metric
    1               Better  X < 10  X < 10  Better  X < 30  X < 30    0.6       0.6         0.9         1.2         0.1             5.0
    2               X < 10  X < 20  X < 30  X < 20  X < 20  X < 20    0.4       0.4         3.4         3.7         4.4             0.3
    3               Better  Better  Better  Better  X < 10  X < 20    1.5       1.5         1.5         0.3         1.5             1.8
    4               X < 10  Better  Same    Same    Same    Same      3.4       3.4         1.8         5.0         5.2             6.8
    5               Same    Better  Same    Same    Same    Same      0.1       0.1         5.0         5.3         5.0             1.8
    6               Same    Same    Same    Better  Better  Better    4.4       4.4         0.3         0.3         5.2             7.4
    7               Same    X < 10  X < 10  X < 10  X < 10  Better    5.0       5.0         1.3         2.1         2.2             0.3
    8               Better  Better  Better  Better  Better  Better    7.8       7.8         5.0         1.5         1.9             7.4
    9               X < 10  X < 10  X < 10  X < 20  X < 30  Better    9.1       9.1         9.4         5.5         5.6             4.6
    10              X < 20  X < 30  X < 30  X < 30  X < 30  X < 30    0.3       0.3         1.5         1.8         2.2             1.5

需要通过SAS实现数据转置与汇总，统计每个月各状态（如Better、X < 10等）的账户数量，同时计算对应状态下的月度指标平均值，期望输出格式如下：

Result  6m  7m  8m  9m  10m 11m Avg_met_6m Avg_met_7m       Avg_met_8m  Avg_met_9m  Avg_met_10m  Avg_met_11m
X < 10  3   3   3   2   3   0       4.3         4.3         3.9             2.9         3.9         2.2
X < 20  1   1   0   1   1   2       0.3         0.3         3.4             0           4.4         0.3
X < 30  0   1   2   1   2   1       0           0           1.5             2.8         2.2         3.3
Same    3   1   3   2   2   2       3.2         3.2         0.3             3.5         5.1         4.3
Better  1   4   2   4   2   4       3.3         3.3         3.3             0.9         2.2         7.4

原始数据集的SAS代码：

data have;
    infile datalines dlm='|';
    input "Account Number"n "6m"n$ "7m"n$ "8m"n$ "9m"n$ "10m"n$ "11m"n$ "6m_Metric"n "7m_Metric"n "8m_Metric"n "9m_Metric"n "10m_Metric"n "11m_Metric"n;
    datalines;
1|Better|X < 10|X < 10|Better|X < 30|X < 30|0.6|0.6|0.9|1.2|0.1|5.0
2|X < 10|X < 20|X < 30|X < 20|X < 20|X < 20|0.4|0.4|3.4|3.7|4.4|0.3
3|Better|Better|Better|Better|X < 10|X < 20|1.5|1.5|1.5|0.3|1.5|1.8
4|X < 10|Better|Same|Same|Same|Same|3.4|3.4|1.8|5.0|5.2|6.8
5|Same|Better|Same|Same|Same|Same|0.1|0.1|5.0|5.3|5.0|1.8
6|Same|Same|Same|Better|Better|Better|4.4|4.4|0.3|0.3|5.2|7.4
7|Same|X < 10|X < 10|X < 10|X < 10|Better|5.0|5.0|1.3|2.1|2.2|0.3
8|Better|Better|Better|Better|Better|Better|7.8|7.8|5.0|1.5|1.9|7.4
9| X < 10|X < 10|X < 10|X < 20|X < 30|Better|9.1|9.1|9.4|5.5|5.6|4.6
10| X < 20|X < 30|X < 30|X < 30|X < 30|X < 30|0.3|0.3|1.5|1.8|2.2|1.5
;
run;

解决方案SAS代码 #

/* 第一步：将宽表转成窄表，把每个月份的状态和指标配对 */
data long;
    set have;
    array status[6] "6m"n "7m"n "8m"n "9m"n "10m"n "11m"n;
    array metrics[6] "6m_Metric"n "7m_Metric"n "8m_Metric"n "9m_Metric"n "10m_Metric"n "11m_Metric"n;
    do month = 1 to 6;
        stat = status[month];
        met = metrics[month];
        month_name = put(month + 5, z2.) || 'm'; /* 生成6m到11m的月份名称 */
        output;
    end;
    keep "Account Number"n stat met month_name;
run;

/* 第二步：按状态和月份分组，统计数量和均值 */
proc sql;
    create table summary as
    select stat as Result,
           sum(case when month_name='6m' then 1 else 0 end) as "6m"n,
           sum(case when month_name='7m' then 1 else 0 end) as "7m"n,
           sum(case when month_name='8m' then 1 else 0 end) as "8m"n,
           sum(case when month_name='9m' then 1 else 0 end) as "9m"n,
           sum(case when month_name='10m' then 1 else 0 end) as "10m"n,
           sum(case when month_name='11m' then 1 else 0 end) as "11m"n,
           mean(case when month_name='6m' then met else . end) as Avg_met_6m format=10.1,
           mean(case when month_name='7m' then met else . end) as Avg_met_7m format=10.1,
           mean(case when month_name='8m' then met else . end) as Avg_met_8m format=10.1,
           mean(case when month_name='9m' then met else . end) as Avg_met_9m format=10.1,
           mean(case when month_name='10m' then met else . end) as Avg_met_10m format=10.1,
           mean(case when month_name='11m' then met else . end) as Avg_met_11m format=10.1
    from long
    group by stat
    order by Result desc;
quit;

/* 查看结果 */
proc print data=summary noobs;
run;

代码说明 #

1. 转置宽表为窄表：使用数组遍历每个月份的状态和指标字段，生成包含账户编号、状态、指标值、月份名称的窄表，方便后续分组统计。
2. 分组统计：通过PROC SQL的条件聚合，按状态分组，统计每个月份的账户数量（用SUM(CASE...)）和指标平均值（用MEAN(CASE...)），最后按状态排序输出。

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