Write multiple SAS datasets into one Excel sheet

Question

I am using SAS enterprise guide 7.15. I want to export several datasets into multiple excel sheets (multiple tables in every sheet). I'm using ODS and even though i'm setting sheet_interval="none", after two tables it breaks the page, and shoves the next tables to another excel sheet.

this is an example to my code, here it's exporting 2 tables, later I want to add 20 tables more in the same sheet:

%macro table_1;

    proc report data=table_1 out=sheet_1 headline split='#' spacing=1 nowd missing
    style(summary)={font_weight=bold}
    columns 
    
    segment     
    diff        
    cnt_old         
    cnt_new
    ;
    
    
    compute before _page_/style=[font_size=3 font_weight=bold foreground=white background=Dark Blue];
    line "table 1";
    line ' ';
    line 'Number of Customers';
    endcomp;
    compute after;
    endcomp
    run;

%mend table_1;

%macro table_3;

    proc report data=table_3 out=sheet_1 headline split='#' spacing=1 nowd missing
    style(summary)={font_weight=bold}
    columns 
    
    FinalRiskRating 
    diff
    cnt_old         
    cnt_new;
    
    
    compute before _page_/style=[font_size=3 font_weight=bold foreground=white background=Dark Blue];
    line "table 3";
    
    endcomp;
    compute after;
    endcomp
    run;

%mend table_3;

%table_1; %table_3;


%let shk = table_1 + table_3;
ods path work.temptemp(update) sasuser.templat(update) sashelp.tmplmst(read);
ods path show;
Options mprint mlogic symbolgen nobyline;
ods listing close;

%macro b;

    %do i=1 %to 2;
    %let mshk=%scan(&shk., &i.,+);
    /*ods tagsets.excelxp options(SHEET_INTERVAL='NONE' PAGEBREAK="NO"  sheet_name="sheet1" ABSOLUTE_COLUMN_WIDTH='8' AUTOFIT_HEIGHT='yes' embed_titles_once = 'yes' embedded_titles='yes');*/
    ods tagsets.excelxp options(sheet_interval="none" sheet_name="sheet1" ABSOLUTE_COLUMN_WIDTH='8' AUTOFIT_HEIGHT='yes' embed_titles_once = 'yes' embedded_titles='yes');
    %&mshk.;
    %end;

%mend b;

ods tagsets.excelxp file="&reportlocation";

%b;
ods tagsets.excelxp close;
ods _all_ close;
;

Answer 1

My suspicion is because you don't specify sheet_interval='none' on the initial ods tagsets.excelxp.

Like yours, the first example has this problem:

ods tagsets.excelxp  file="h:\temp\test.xls";
ods tagsets.excelxp options(sheet_interval='none');
proc print data=sashelp.class;
run;
ods tagsets.excelxp options(sheet_interval='none');
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;

But this works as expected:

ods tagsets.excelxp options(sheet_interval='none') file="h:\temp\test.xls";
proc print data=sashelp.class;
run;
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;

Interestingly, if you remove the second one, it still works - so it's not exactly that it's not on the first line, but rather it's the new options statement. I guess that resets it somehow. But in your case there's really no reason not to put any of those details on the initial ods tagsets.excelxp statement.

ODS EXCEL doesn't work that way, it always goes all on one sheet. I'd recommend using that, if you can, in any event.