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;
;

Since your question is about SAS code the version of SAS that your Enterprise Guide front end is connecting too is more important than the version of the front end. Your posted program runs the table generating macros twice. Does that explain what you are seeing? — Tom, Jul 25 '21 at 14:29
Use `proc product_status;run;` to have SAS display your SAS version in the log, it should be something like SAS 9.4M6. If you have a 9.4M5+ version I strongly recommend you switch to ODS EXCEL. Also, what version of TAGSETS.EXCELXP are you using? That information is also in the log. If it's not version 1.131 you need to update it as well. — Reeza, Jul 26 '21 at 15:47

score 1 · Accepted Answer · answered Jul 26 '21 at 15:47

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.

Thank you all for the comments. Joe's advice helped me. – Mashdool Jul 27 '21 at 05:25 — Mashdool, Jul 27 '21 at 05:25

Write multiple SAS datasets into one Excel sheet

1 Answers1