在Proc SQL中使用多个end as语句时出现语法错误

我用以下Proc SQL代码给以_val结尾的变量赋值并创建新变量，部分变量加_val后名称太长，所以尝试用DO循环和%if %then %else条件缩短名称，但运行时报错。

宏代码 #

%macro rn_do;
proc sql noprint;
    create table validate_select2 as
    select *
    %do a=1 %to &checkbox_select_n;
        %do b=1 %to %eval(&&variable_checkbox_select_max&a);
            ,case when &b=1 &&&variable_checkbox_select&a..&&b. in (select valid_values from response_options_scan where variable="&&variable_checkbox_select&a") then "&&variable_checkbox_select&a&&b valid value"
            when &b=1 &&&variable_checkbox_select&a..&&b.="" and (select requirement_status from response_options_select where variable="&&variable_checkbox_select&a") in ("All Records (Backend data)","All Records", "Required") then "&&variable_checkbox_select&a&&b invalid value: missing"
            when &b=1 &&&variable_checkbox_select&a..&&b.="" and (select requirement_status from response_options_select where variable="&&variable_checkbox_select&a") then "&&variable_checkbox_select&a&&b invalid value: missing"
            when &b=1 &&&variable_checkbox_select&a..&&b.="" then "&&variable_checkbox_select&a&&b valid value: missing"
            when &b ne 1 &&&variable_checkbox_select&a..&&b.="" then "&&variable_checkbox_select&a&&b valid value: missing"
            when &b ne 1 and strip(&&variable_checkbox_select&a..&&b.) in (select valid_values from response_options_scan where variable="&&variable_checkbox_select&a") then "&&variable_checkbox_select&a&&b valid value"
            else "&&variable_checkbox_select&a&&b invalid value"
            %do c=1 %to 3;
                %if &&variable_checkbox_select&a=child&c._coadministered_vaccine %then %do;
                    end as child&c._coadministered_vax&b._val format=$100.
                %end;
                %else %if &&variable_checkbox_select&a=coadministered_vaccine_other %then %do;
                    end as coadministered_vax_other&b._val format=$100.
                %end;
                %else %do;
                    end as &&variable_checkbox_select&a..&&b._val format=$100.
                %end;
            %end;
        %end;
    %end;
    from validate_select;
quit;
%mend rn_do;

%rn_do;

错误日志 #

NOTE: Line generated by the invoked macro "RN_DO".
 169               end as &&variable_checkbox_select&a..&&b._val format=$100.
                   ___
                   22
 NOTE 137-205: Line generated by the invoked macro "RN_DO".
 169               end as &&variable_checkbox_select&a..&&b._val format=$100.
                                                                ______
                                                                22
 ERROR 22-322: Syntax error, expecting one of the following: ',', AS, FROM.  

 ERROR 22-322: Syntax error, expecting one of the following: GROUP, HAVING, ORDER, WHERE.  

 NOTE: Line generated by the invoked macro "RN_DO".
 169               end as &&variable_checkbox_select&a..&&b._val format=$100.
                                                                ______
                                                                76
 ERROR 76-322: Syntax error, statement will be ignored.

错误原因分析 #

• 多重循环导致END重复输出：内层%do c=1 %to 3循环会让单个CASE语句生成3个END as ...语句，违反SQL语法——一个CASE只能对应一个END闭合，重复的END会让SAS解析器混淆。
• 变量名包含非法字符：&&variable_checkbox_select&a..&&b._val里的连续点号，会被解析为变量名的一部分，但SAS数据集变量名不允许包含点号，直接触发语法错误。

解决方案 #

1. 移除多余的%do c循环，改用单次条件分支 #

把循环替换为逐个判断的条件分支，确保每个CASE只输出一个END：

%macro rn_do;
proc sql noprint;
    create table validate_select2 as
    select *
    %do a=1 %to &checkbox_select_n;
        %do b=1 %to %eval(&&variable_checkbox_select_max&a);
            ,case when &b=1 &&&variable_checkbox_select&a..&&b. in (select valid_values from response_options_scan where variable="&&variable_checkbox_select&a") then "&&variable_checkbox_select&a&&b valid value"
            when &b=1 &&&variable_checkbox_select&a..&&b.="" and (select requirement_status from response_options_select where variable="&&variable_checkbox_select&a") in ("All Records (Backend data)","All Records", "Required") then "&&variable_checkbox_select&a&&b invalid value: missing"
            when &b=1 &&&variable_checkbox_select&a..&&b.="" and (select requirement_status from response_options_select where variable="&&variable_checkbox_select&a") then "&&variable_checkbox_select&a&&b invalid value: missing"
            when &b=1 &&&variable_checkbox_select&a..&&b.="" then "&&variable_checkbox_select&a&&b valid value: missing"
            when &b ne 1 &&&variable_checkbox_select&a..&&b.="" then "&&variable_checkbox_select&a&&b valid value: missing"
            when &b ne 1 and strip(&&variable_checkbox_select&a..&&b.) in (select valid_values from response_options_scan where variable="&&variable_checkbox_select&a") then "&&variable_checkbox_select&a&&b valid value"
            else "&&variable_checkbox_select&a&&b invalid value"
            %* 替换原循环为单个条件分支，避免重复输出END;
            %if &&variable_checkbox_select&a=child1_coadministered_vaccine %then %do;
                end as child1_coadministered_vax&b._val format=$100.
            %end;
            %else %if &&variable_checkbox_select&a=child2_coadministered_vaccine %then %do;
                end as child2_coadministered_vax&b._val format=$100.
            %end;
            %else %if &&variable_checkbox_select&a=child3_coadministered_vaccine %then %do;
                end as child3_coadministered_vax&b._val format=$100.
            %end;
            %else %if &&variable_checkbox_select&a=coadministered_vaccine_other %then %do;
                end as coadministered_vax_other&b._val format=$100.
            %end;
            %else %do;
                %* 修正变量名，用下划线替换点号;
                end as &&variable_checkbox_select&a._&&b._val format=$100.
            %end;
        %end;
    %end;
    from validate_select;
quit;
%mend rn_do;

%rn_do;

2. 优化变量命名规则 #

将变量名中的点号替换为下划线，确保生成的变量名符合SAS命名规范（仅允许字母、数字、下划线，且不能以数字开头）。

3. 可选：用宏函数批量简化变量名 #

如果需要处理更多长变量名，可使用%sysfunc(tranwrd)批量替换关键字：

%else %do;
    %let short_var = %sysfunc(tranwrd(&&variable_checkbox_select&a, coadministered_vaccine, coadministered_vax));
    end as &short_var._&&b._val format=$100.
%end;

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