I want to export to multiple excel sheets of same file from sas!

[dkanand86]

Hi Everyone,

I want to export the results for each macro run into different sheets of same excel file. But, unable to get that kind of result.

Please help! Thanks!

Code is below:

rsubmit;

options compress=yes mprint symbolgen mlogic;

%macro cs_new2(base_dsn=,recent_dsn=,var_name=,n=);

ods tagsets.excelxp style=minimal file="location/cs_r_mod.xls";

ods tagsets.excelxp options(sheet_name="Sheet&n.");

/*ods html file="location/cs_&var_name._new.xls";*/

proc freq data=&base_dsn.;

table &var_name. /list missing out=base_&var_name. (rename=(percent=baseline_pct count=baseline_N));

run;

proc freq data=&recent_dsn.;

table &var_name./list missing out=recent_&var_name.(rename=(percent=recent_pct count=recent_N));

run;

ods tagsets.excelxp close;

%mend cs_new2;

endrsubmit;

rsubmit;

%cs_new2(base_dsn=sph_lds,recent_dsn=data_v1,var_name=VAR1,n=1);

%cs_new2(base_dsn=sph_lds,recent_dsn=data_v1,var_name=VAR2,n=2);

%cs_new2(base_dsn=sph_lds_dsn=data_v1,var_name=VAR3,n=3);

%cs_new2(base_dsn=sph_lds,recent_dsn=data_v1,var_name=VAR4,n=4);

%cs_new2(base_dsn=sph_lds,recent_dsn=data_v1,var_name=VAR5,n=5);

%cs_new2(base_dsn=sph_lds,recent_dsn=data_v1,var_name=VAR6,n=6);

endrsubmit;

[RW9]

You have the line:

ods tagsets.excelxp style=minimal file="location/cs_r_mod.xls";

In your macro.  Everytime this code runs the file is created anew.  Put this line outside your macro calls:

ods tagsets.excelxp style=minimal file="location/cs_r_mod.xls";

%cs_new2(...);

%cs_new2(...);

ods tagsets.excelxp close;

Only change the sheet name in the macro.