使用SAS Proc Freq/Proc SQL实现两组数据集教育水平频次汇总统计

需要编写SAS代码（使用Proc Freq或Proc SQL），对以下两个数据集按教育水平进行频次统计，并生成包含指定汇总项的结果表。

数据集定义 #

Group1 #

DATA Group1;
INPUT Subject_ID education;
DATALINES;
1 LowerPrimary
2 Secondary
3 UpperPrimary
4 LowerPrimary
5 Secondary
6 Secondary
7 LowerPrimary
8 UpperPrimary
9 UpperPrimary
10 Secondary
;
RUN;

Group2 #

DATA Group2;
INPUT Subject_ID education;
DATALINES;
1 LowerPrimary
2 Secondary
3 UpperPrimary
4 LowerPrimary
5 Secondary
6 Secondary
7 LowerPrimary
8 UpperPrimary
9 UpperPrimary
10 Secondary
11 LowerPrimary
12 UpperPrimary
;
RUN;

期望输出 #

Lower_Primary Upper_Primary Primary_Total Secondary Group_Total
Group1      3               3            6           4          10
Group2      4               4            8           4          12

汇总规则 #

• Primary_Total = Lower_Primary + Upper_Primary
• Group_Total = Lower_Primary + Upper_Primary + Secondary

解决方案 #

方法1：使用Proc SQL（推荐，步骤更简洁） #

先合并两个数据集并标记组别，再通过条件统计直接计算各分类频次与汇总项：

/* 合并数据集并标记组别 */
DATA combined;
    SET Group1(in=g1) Group2(in=g2);
    IF g1 THEN Group = 'Group1';
    ELSE IF g2 THEN Group = 'Group2';
RUN;

/* 统计频次并计算汇总 */
PROC SQL;
    CREATE TABLE final_result AS
    SELECT Group,
           SUM(CASE WHEN education='LowerPrimary' THEN 1 ELSE 0 END) AS Lower_Primary,
           SUM(CASE WHEN education='UpperPrimary' THEN 1 ELSE 0 END) AS Upper_Primary,
           SUM(CASE WHEN education IN ('LowerPrimary','UpperPrimary') THEN 1 ELSE 0 END) AS Primary_Total,
           SUM(CASE WHEN education='Secondary' THEN 1 ELSE 0 END) AS Secondary,
           COUNT(*) AS Group_Total
    FROM combined
    GROUP BY Group;
QUIT;

/* 打印结果 */
PROC PRINT DATA=final_result NOOBS;
RUN;

方法2：使用Proc Freq + 数据步 #

先通过Proc Freq统计各教育水平的频次，转置后计算汇总项：

/* 处理Group1 */
PROC FREQ DATA=Group1 NOPRINT;
    TABLES education / OUT=freq_g1(WHERE=(education NE ''));
RUN;

PROC TRANSPOSE DATA=freq_g1 OUT=trans_g1 PREFIX=;
    VAR COUNT;
    ID education;
RUN;

DATA trans_g1;
    SET trans_g1;
    Group = 'Group1';
    Primary_Total = LowerPrimary + UpperPrimary;
    Group_Total = Primary_Total + Secondary;
    RENAME LowerPrimary=Lower_Primary UpperPrimary=Upper_Primary;
RUN;

/* 处理Group2 */
PROC FREQ DATA=Group2 NOPRINT;
    TABLES education / OUT=freq_g2(WHERE=(education NE ''));
RUN;

PROC TRANSPOSE DATA=freq_g2 OUT=trans_g2 PREFIX=;
    VAR COUNT;
    ID education;
RUN;

DATA trans_g2;
    SET trans_g2;
    Group = 'Group2';
    Primary_Total = LowerPrimary + UpperPrimary;
    Group_Total = Primary_Total + Secondary;
    RENAME LowerPrimary=Lower_Primary UpperPrimary=Upper_Primary;
RUN;

/* 合并结果并打印 */
DATA final_result;
    SET trans_g1 trans_g2;
RUN;

PROC PRINT DATA=final_result NOOBS;
RUN;

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