在SAS中转置多列数据并生成计数与占比统计

需求说明 #

现有如下SAS数据集，其中Account Number为账户编号，6m至11m列记录对应账户在第6至11个月的状态（如Better、X < 10等）。需要将该数据集转置为按状态（Result）统计各月份出现次数的格式，同时生成各状态在对应月份的占比百分比。

原始数据集 #

Account Number  6m      7m      8m      9m      10m     11m
1               Better  X < 10  X < 10  Better  X < 30  X < 30
2               X < 10  X < 20  X < 30  X < 20  X < 20  X < 20
3               Better  Better  Better  Better  X < 10  X < 20
4               X < 10  Better  Same    Same    Same    Same
5               Same    Better  Same    Same    Same    Same
6               Same    Same    Same    Better  Better  Better
7               Same    X < 10  X < 10  X < 10  X < 10  Better
8               Better  Better  Better  Better  Better  Better
9               X < 10  X < 10  X < 10  X < 20  X < 30  Better
10              X < 20  X < 30  X < 30  X < 30  X < 30  X < 30

目标计数数据集 #

Result  6m  7m  8m  9m  10m 11m
X < 10  3   3   3   1   2   0
X < 20  1   1   0   2   1   2
X < 30  0   1   1   1   2   1
Same    3   1   3   2   2   2
Better  1   2   1   2   2   4

原始数据集SAS代码 #

data have;
    infile datalines dlm='|';
    input "Account Number"n "6m"n$ "7m"n$ "8m"n$ "9m"n$ "10m"n$ "11m"n$;
    datalines;
1|Better|X < 10|X < 10|Better|X < 30|X < 30
2|X < 10|X < 20|X < 30|X < 20|X < 20|X < 20
3|Better|Better|Better|Better|X < 10|X < 20
4|X < 10|Better|Same|Same|Same|Same
5|Same|Better|Same|Same|Same|Same
6|Same|Same|Same|Better|Better|Better
7|Same|X < 10|X < 10|X < 10|X < 10|Better
8|Better|Better|Better|Better|Better|Better
9| X < 10|X < 10|X < 10|X < 20|X < 30|Better
10| X < 20|X < 30|X < 30|X < 30|X < 30|X < 30
;
run;

解决方案 #

步骤1：将宽表转置为窄表 #

先把原始的宽格式数据（每一行对应一个账户的所有月份状态）转换为窄格式（每一行对应一个账户的单个月份状态），方便后续统计：

/* 宽表转窄表，提取月份和对应状态 */
data temp;
    set have;
    /* 定义数组存储所有月份列 */
    array months[6] "6m"n "7m"n "8m"n "9m"n "10m"n "11m"n;
    do i = 1 to dim(months);
        month = vname(months[i]); /* 获取月份列名 */
        result = strip(months[i]); /* 去除状态值前后空格，避免统计误差 */
        output;
    end;
    /* 保留需要的变量 */
    keep "Account Number"n month result;
run;

步骤2：生成计数统计数据集 #

通过SQL语句统计每个状态在各月份的出现次数，转置为目标宽格式：

/* 统计各状态在各月份的出现次数 */
proc sql;
    create table count_result as
        select result,
               sum(case when month = '6m' then 1 else 0 end) as "6m"n,
               sum(case when month = '7m' then 1 else 0 end) as "7m"n,
               sum(case when month = '8m' then 1 else 0 end) as "8m"n,
               sum(case when month = '9m' then 1 else 0 end) as "9m"n,
               sum(case when month = '10m' then 1 else 0 end) as "10m"n,
               sum(case when month = '11m' then 1 else 0 end) as "11m"n
        from temp
        group by result
        order by result;
quit;

执行后得到的count_result即为目标计数数据集。

步骤3：生成百分比占比数据集 #

基于计数结果，计算各状态在对应月份的占比（占当月总账户数的百分比）：

方式1：已知总账户数（固定为10）

如果确定每个月的总记录数等于账户数（10），可以直接计算：

/* 生成百分比占比数据集（总账户数固定为10） */
data pct_result;
    set count_result;
    total = 10;
    /* 定义数组存储所有计数列 */
    array counts[6] "6m"n-"11m"n;
    do i = 1 to dim(counts);
        counts[i] = (counts[i]/total)*100;
        format counts[i] 5.1; /* 设置格式，保留1位小数 */
    end;
    drop total i;
run;

方式2：动态计算各月份总记录数

如果账户数不固定，先统计每个月的总记录数，再计算占比：

/* 统计各月份总记录数 */
proc sql;
    create table month_total as
        select month, count(*) as total
        from temp
        group by month;
quit;

/* 计算各状态在各月份的占比百分比 */
proc sql;
    create table pct_result as
        select t.result,
               (sum(case when t.month = '6m' then 1 else 0 end)/m6.total)*100 as "6m"n format=5.1,
               (sum(case when t.month = '7m' then 1 else 0 end)/m7.total)*100 as "7m"n format=5.1,
               (sum(case when t.month = '8m' then 1 else 0 end)/m8.total)*100 as "8m"n format=5.1,
               (sum(case when t.month = '9m' then 1 else 0 end)/m9.total)*100 as "9m"n format=5.1,
               (sum(case when t.month = '10m' then 1 else 0 end)/m10.total)*100 as "10m"n format=5.1,
               (sum(case when t.month = '11m' then 1 else 0 end)/m11.total)*100 as "11m"n format=5.1
        from temp t
        left join month_total m6 on t.month='6m' and m6.month='6m'
        left join month_total m7 on t.month='7m' and m7.month='7m'
        left join month_total m8 on t.month='8m' and m8.month='8m'
        left join month_total m9 on t.month='9m' and m9.month='9m'
        left join month_total m10 on t.month='10m' and m10.month='10m'
        left join month_total m11 on t.month='11m' and m11.month='11m'
        group by t.result
        order by t.result;
quit;

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