如何将SAS宏%IF语句与PROC SQL SELECT语句结合创建表

需要在输出表中创建多列，其中8-9列依赖同一条件判断，希望避免重复使用CASE WHEN以降低代码复杂度。作为SAS新手，编写的宏代码无法运行，同时不使用宏的方案需要重复写相同条件的CASE WHEN超过10次，代码冗余。

错误的宏代码 #

%macro DPAPRDT:
proc sql;
    execute(
        create table test as (
          %IF table2.A < table2.C and table2.A > table3.D 
             %then 
               %do 
               select table1.A,
                      table1.B,
                      table2.D,
                      table2.E,
                      table2.F,
              
             %else
               %do
              select  table1.A,
                      table1.B,
                      table3.D,
                      table3.E,
                      table3.F
                %end;
               from table1 
               join table2 on ... 
               join table3 on ... 
  
       WITH DATA PRIMARY INDEX (table1.A, table1.B) 
    

%mend DPAPRDT;

编译错误信息 #

ERROR: Expected semicolon not found.  The macro will not be compiled.
ERROR: A dummy macro will be compiled.
ERROR: Expected %TO not found in %DO statement.

无宏的冗余实现 #

proc sql;
    execute(
        create table test as (
               select table1.A,
                      table1.B,
                      case when table2.A < table2.C and table2.A > table3.D 
                           then table2.D else table3.D end as return1,
                      case when table2.A < table2.C and table2.A > table3.D 
                           then table2.E else table3.E end as return2,
                      case when table2.A < table2.C and table2.A .. 
                      case when table2.A < table2.C and table2.A .. 
                         . 
                         .
                         .
                     from table1 
                     join table2 on ... 
                     join table3 on ... 
               )WITH DATA PRIMARY INDEX (table1.A, table1.B) 
 end;

方案1：用宏变量定义重复条件，简化CASE WHEN #

将重复的判断条件定义为宏变量，在每个CASE WHEN中引用，减少代码重复：

%macro DPAPRDT;
/* 把重复判断条件定义为宏变量，后续直接引用 */
%let condition = table2.A < table2.C and table2.A > table3.D;
proc sql;
    execute(
        create table test as (
               select table1.A,
                      table1.B,
                      case when &condition then table2.D else table3.D end as return1,
                      case when &condition then table2.E else table3.E end as return2,
                      case when &condition then table2.F else table3.F end as return3,
                      /* 按需添加剩余需要判断的列 */
                     from table1 
                     join table2 on ... 
                     join table3 on ... 
               ) WITH DATA PRIMARY INDEX (table1.A, table1.B) 
    );
quit;
%mend DPAPRDT;

/* 调用宏执行 */
%DPAPRDT;

方案2：用宏循环批量生成列定义 #

如果需要处理的列数量较多，用宏循环自动生成所有CASE WHEN语句，进一步简化代码：

%macro DPAPRDT;
%let condition = table2.A < table2.C and table2.A > table3.D;
/* 列出所有需要按条件判断的列名，空格分隔 */
%let cols = D E F G H I J K;
proc sql;
    execute(
        create table test as (
               select table1.A,
                      table1.B,
                      /* 循环生成每一列的CASE WHEN语句 */
                      %do i=1 %to %sysfunc(countw(&cols));
                          %let col = %scan(&cols,&i);
                          case when &condition then table2.&col else table3.&col end as return&i
                          /* 最后一列后不加逗号 */
                          %if &i < %sysfunc(countw(&cols)) %then ,;
                      %end;
                     from table1 
                     join table2 on ... 
                     join table3 on ... 
               ) WITH DATA PRIMARY INDEX (table1.A, table1.B) 
    );
quit;
%mend DPAPRDT;

%DPAPRDT;

原宏代码错误说明 #

1. 宏定义语法错误：%macro DPAPRDT:末尾多了冒号，正确写法是%macro DPAPRDT;
2. 错误使用%do：%do需要配合%to用于循环，原代码只是分支判断，不需要%do块
3. 宏逻辑错误：%IF是编译时执行的条件判断，无法直接引用数据集的列值（如table2.A），必须用数据层面的CASE WHEN处理行级判断

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