The SAS Output Delivery System and reporting techniques

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

Reply
Contributor
Posts: 52

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

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;

Esteemed Advisor
Esteemed Advisor
Posts: 6,674

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

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.

Post a Question
Discussion Stats
  • 1 reply
  • 231 views
  • 0 likes
  • 2 in conversation