ODS excel sheet_interval - separate into multiple sheets

[rmh1999]

Was wondering if there is a simple way to use sheet_interval to make a new sheet after every five outputs/tables. If I use sheet_interval="none", every table is outputted into one big sheet. Ideally, I'd like to have 19 sheets each with five tables.

Pretty sure I cannot copy/paste my code so please bare with me...

[ballardw]

How many procedures are involved? How many tables does each procedure call create?

If you can't share your exact code because it is proprietary then make something up that isn't that is similar in nature and probably should use one or more of the data sets in the SASHELP library to demonstrate.

Since basically none of the SAS procedures have a way of telling use beforehand how many tables will be created you will likely have to modify code in some way to fit an arbitrary number of tables per page. How difficult that may be depends on the output and the procedures involved.

[Reeza]

If you cannot share your data, try doing it with a data set from sashelp, cars, heart, class are common ones. This way you're not sharing your private data but you're more likely to get an answer that will work for you.

---

@rmh1999 wrote:

Was wondering if there is a simple way to use sheet_interval to make a new sheet after every five outputs/tables. If I use sheet_interval="none", every table is outputted into one big sheet. Ideally, I'd like to have 19 sheets each with five tables.

Pretty sure I cannot copy/paste my code so please bare with me...

---

[Reeza]

HW? https://communities.sas.com/t5/SAS-Programming/How-to-set-sheet-interval-for-for-ods-excel-as-a-new-...

You could also make a fake variable that would allow you to group them by 5. If you need help with that please post a sample of your data.

[acordes]

between ods excel and ods close try to put this code and wrap it additionally into an if-then statement that resolves true for ^mod(i,5) to trigger the output after each sequence of 5 tables.

ods excel options(sheet_interval='output');
  ods exclude all;
 data _null_;
  dcl odsout obj();
 run;
 ods select all;

[rmh1999]

%macro test(year=%str(),mon1=str%(),mon2=str%(), id=str%(),element=str%()); 
.
.
.
ods excel options(sheet_name="&id. &idname." sheet_interval="none" embedded_titles='yes');

title;
proc odstext;
  p "Long title" / style=[color=black font_weight=bold];
  p "";
  p "&elem." / style=[color=black font_weight=bold];
run;

proc print data=output_&elem._&idname. noobs;
run;

%mend test;

ods excel file="C:\Users\name\Documents\test\Tabs Test.xlsx";

%test (year=2021,mon1=Jan,mon2=Feb,id=1,element=elem1);
%test (year=2021,mon1=Jan,mon2=Feb,id=1,element=elem2);
%test (year=2021,mon1=Jan,mon2=Feb,id=1,element=elem3);
%test (year=2021,mon1=Jan,mon2=Feb,id=1,element=elem4);
%test (year=2021,mon1=Jan,mon2=Feb,id=1,element=elem5);
%test (year=2021,mon1=Jan,mon2=Feb,id=2,element=elem1);
%test (year=2021,mon1=Jan,mon2=Feb,id=2,element=elem2);
%test (year=2021,mon1=Jan,mon2=Feb,id=2,element=elem3);
%test (year=2021,mon1=Jan,mon2=Feb,id=2,element=elem4);
%test (year=2021,mon1=Jan,mon2=Feb,id=2,element=elem5);

ods excel close;

Thank you for your responses. After going through each, I've decided to share some of my relevant code (altered slightly of course).

The three dots at the top indicate sections of code I've omitted that brings in data, matches specific data, and creates the tables I need for my output. So far, this code generates ten tables onto a single sheet. I would like to ideally have a tab for each ID with five tables for each element. Hopefully this gives a rough idea of what I have and what I'm trying to do...

I'm pretty much a beginner in SAS, so again please bare with me.

[Reeza]

Change Sheet_interval option from NONE to NOW or PROC .

[rmh1999]

I've tried it, but it separates each table into it's own tab rather than one tab per five tables...

[Reeza]

Seems like you want it to reset at elem1? If so, add some conditional logic to your macro that generates a new sheet as needed?

%if &elem = elem1 %then %do;
ods excel options(......sheet_interval='now');
%end;
%else %do;
ods excel options sheet_interval = 'none');
%end;

[rmh1999]

Okay, so this works for the most part (thank you!). The only issue is that it puts the very first table for elem1 on it's own sheet rather than with the other four tables for that first ID....