使用输入规范生成 SAS 代码

我不会涉及 VBA。我会编写一个 SAS 程序来读取需求，生成表格并将它们推送回新的 Excel 程序。如果你想完全从 Excel 程序中驱动它，我会推荐 SAS Add-In for Excel 而不是 VBA 代码，尽管你可以通过 VBA 触发它。Chris Hemidinger 是这些类型问题的 SAS 专家，他确实在 community.sas.com 上发帖，因为另一位用户表示您应该在那里发帖（在评论中）。

创建空表是一个微不足道的过程，但报告表通常具有非常具体的布局，因此您可以进一步概括此过程。例如，我有一个宏可以为数据集创建我的标准表 1（特征表），我只需要按类型（连续、分类、二进制）指定输入变量，然后生成输出并可以推送通过 ODS EXCEL 表现出色。我还使用 PROC REPORT 对其进行格式化，因为我将为不同的变量交替显示行，以便更容易阅读/显示。

/*
This macro creates a table of charateristics for the variables listed.  
It handles categorical, binary and continuous variables and produces and output dataset that can be further customized. No statistical information is
included in this analysis
*/

/*Parameters to be set:
dsetin - name of dataset to be analyzed
cont = macro variable list of variable names, ie cont=age weight height
cat=list of categorical variables ie cat=sex grade
bin=binary variables, such as smoking now, smoking ever
dsetout=name of output dataset
Run example at the end for a sample output dataset call sample_table_char in the work directory
*/


*options mprint symbolgen;
%macro table_char(dsetin, cont, cat, bin, dsetout);


*delete old dataset;

proc datasets nodetails nolist;
    delete &dsetout;
quit;

/****************************************************************
Handle Categorical Variables
****************************************************************/

*loop through variable list;
%let i=1;
%do %while (%scan(&cat, &i, " ") ^=%str());
%let var=%scan(&cat, &i, " ");  

*Get format for variable;
data _null_;
 set &dsetin;
 call symput("var_fmt", vformat(&var));
run;



proc freq data=&dsetin noprint;
    table &var/missing out=tab_var;
run;



data temp1;
    length categorical $200.; format categorical $200.;
    length value $200.; format value $200.;

    set tab_var;
    percent=percent/100;
    categorical=put(&var., &var_fmt.);
    if _n_=1 then do;
        value=put(count, 8.)||"("||compress(put(percent, percent8.1))||")";
        order=2;
        output;
        order=1;
        value='';
        categorical=propcase(vlabel(&var.));
        output;
    end;
    else do;
        order=2;
        value=put(count, 8.)||"("||compress(put(percent, percent8.1))||")";
        output;

    end;

    keep categorical value order;
run;

proc sort data=temp1 out=temp2 (drop=order); by order categorical; run;


proc append base=&dsetout data=temp2;
run;

*clean up;

proc datasets nodetails nolist;
    delete tab_var temp1 temp2;
run; quit;

*Increment counter;
%let i=%eval(&i+1);
%end; *Categorical;

/****************************************************************
Handle Continuous Variables
****************************************************************/

%let i=1;
%do %while (%scan(&cont, &i, " ") ^=%str());
%let var=%scan(&cont, &i, " "); 



proc means data=&dsetin (rename=&var=vn) noprint;
var vn;
output out=table_var n= nmiss= mean= min= max= std= median= p25= p75= p90=/autoname;
run;

*get label of variable for clean reporting;
data _null_;
 set &dsetin;
 call symput("var_label", vlabel(&var));
run;




data temp1;
    length categorical $200.; format categorical $200.;
    format value $200.; length value $200.;

    set table_var;

    categorical="&var_label.";
    value=.;
    output;

    categorical='Count(Missing)';
    value=put(vn_n, 5.)||"("||compress(put(vn_nmiss, 5.))||")";
    output;

    categorical='Mean (SD)';
    value=put(vn_mean, 8.1)||"("||compress(put(vn_stddev, 8.1))||")";
    output;

    categorical='Median (IQR)';
    value=put(vn_median, 8.1)||"("||compress(put(vn_p25, 8.1))||" - "||compress(put(vn_p75, 8.1))||")";
    output;

    categorical='Range';
    value=put(vn_min, 8.1)||" - "||compress(put(vn_max, 8.1));
    output;

    categorical='90th Percentile';
    value=put(vn_p90, 8.1);
    output;



    keep categorical value;
run;


proc append base=&dsetout data=temp1;
run;

*clean up;

proc datasets nodetails nolist;
    delete table_var temp1;
run; quit;

*Increment counter;
%let i=%eval(&i+1);
%end; *Continuous;

/*****************************************************************
Handle Binary Variables (only report 1s)
*****************************************************************/

%let i=1;
%do %while (%scan(&bin, &i, " ") ^=%str());
%let var=%scan(&bin, &i, " ");  



proc freq data=&dsetin noprint;
    table &var/missing out=tab_var;
run;

data tab_var;
    set tab_var;
    where &var=1;
run;

data temp1;
    length categorical $200.; format categorical $200.;
    length value $200.; format value $200.;

    set tab_var;
    percent=percent/100;
    if _n_=1 then do;
        value=put(count, 8.)||"("||compress(put(percent, percent8.1))||")";
        order=1;
        categorical=propcase(vlabel(&var.));
        output;
    end;
    keep categorical value;
run;




proc append base=&dsetout data=temp1;
run;

*clean up;

proc datasets nodetails nolist;
    delete tab_var temp1;
run; quit;

*Increment counter;
%let i=%eval(&i+1);
%end;*Binary;


%mend table_char;


/* *Example of macro usage; */
/* data sample; */
/*  set sashelp.class; */
/*  female=ifn( sex='F',1,0); */
/* run; */
/*  */
/*  */
/* %table_char(sample, height weight age, sex, female, sample_table_char); */