ods excel - example of use of sheet_interval='page'

[xxformat_com]

Hi,

I was trying to find an example where sheet_interval='page' would work but couldn't. The following example does not create a new tab every 15 or so observations.

data class;
    set sashelp.class;
    do x=1 to 10;
        output;
    end;
run;

options pagesize=15;

ods excel file="&xxdemo./reporting/test.xlsx";

ods excel options(sheet_interval='page');
                  
*proc print data=class noobs; 
run;

proc report data=class;
    columns sex;
    define sex / display;
run;

ods excel close;

I also could not find a difference in result, except the tab name, between a sheet_interval='table' and a sheet_interval='bygroup' with ods excel, given that by statement create one table per by value. Do you have any example where both differ?

Regards,

Véronique

[Cynthia_sas]

Hi:

  In many instances, sheet_interval='page' and sheet_interval='table' and sheet_interval='bygroup' will give you the same results. However, the sheet_interval='page' is NOT tied to physical pages. It is tied to procedures which produce logical pages, such as using the page dimension in proc tabulate or page processing in PROC REPORT. Your PROC REPORT code isn't doing anything in the code to turn on page processing.

  Your pagesize=15 system option will ONLY apply to the LISTING destination -- the SAS output window. That option is ignored by ODS PDF, HTML, RTF and ODS EXCEL. They all use different methods to handle paging.

  If you want to have every value of X or some variable generate a new page, you have to do that differently in PROC REPORT. You indicated that you wanted to have a "pagesize" of 15 and were looking to get 1 sheet each showing 15 obs. To do that you'll have to try something other than pagesize=15. You'll have to create a helper variable for paging, perhaps like this:

data shoes;
    set sashelp.shoes;
    pagevar = ceil(_n_/15);
	origobs_num = _n_;
run;
 

Note that I used SASHELP.SHOES which has over 300 obs, so it was a good candidate for multiple pages. Then, once I have made my helper variables, I do this in PROC REPORT:

ods excel file="c:\temp\report_page.xlsx" 
    options(sheet_interval='page');
    
proc report data=shoes;
    title '2a) Proc Report using PAGE option';
    columns pagevar origobs_num region product sales;
	define pagevar / group page;
	define origobs_num / display;
    define region / display;
	break after pagevar / page;
run;
ods excel close;

and get these results in Excel:

Then, if you want to suppress the appearance of the helper variables, you use the NOPRINT option on the DEFINE statement for the variables. They have to be on the COLUMN statement and in the program so break processing can occur, but NOPRINT will hide them from the display.

Hope this helps,

Cynthia

Here's an example of using PAGE processing in TABULATE:

ods excel file='c:\temp\tab_page.xlsx'
    options(sheet_interval='page');
proc tabulate data=sashelp.shoes;
  title '1a) Tabulate Sheet_interval=page';
  where region in ('Asia', 'Canada', 'Pacific') and
        Product in ('Boot', 'Sport Shoe', "Women's Dress");
  class product region;
  var sales;
  table product all,
        region all,
		sales*(min mean median max);
run;
ods excel close;


ods excel file='c:\temp\tab_table.xlsx'
    options(sheet_interval='table');
proc tabulate data=sashelp.shoes;
  title '1b) Tabulate sheet_interval=table';
  where region in ('Asia', 'Canada', 'Pacific') and
        Product in ('Boot', 'Sport Shoe', "Women's Dress");
  class product region;
  var sales;
  table product all,
        region all,
		sales*(min mean median max);
run;
ods excel close;

[xxformat_com]

I first thought it was the solution and first accepted it. But as I ran the proc report example and I realized that I got the same resultat whether or not sheet_interval='page' was used.

[Cynthia_sas]

The default is sheet_interval='table' and every logical page is a separate table, so, in this instance they will give you the same thing. So would sheet_interval='bygroup'. I don't understand why it's a problem if they worked the same way.
Cynthia

[xxformat_com]

Your question : "The default is sheet_interval='table' and every logical page is a separate table, so, in this instance they will give you the same thing. So would sheet_interval='bygroup'. I don't understand why it's a problem if they worked the same way.
Cynthia"

My original question is to find one example where sheet_interval='page' would bring something that is not already coverred by sheet_interval. Right now the only benefit of sheet_interval='page' I see is that by default the tab name is slighly more readable than sheet_interval='table'.

I hope it clarifies the question.

[Cynthia_sas]

Hi: For most examples, they will give you the same output. Your original code example was trying to influence sheet_interval with the pagesize system option, which is ignored by ODS, so my original attempt was to show how to get your arbitrary 15 rows per "page" using sheet_interval.

My tendency is to use sheet_interval='bygroup' because then I can automatically name the sheets to match the BY values.
Cynthia

[xxformat_com]

I'm also wondering how the tab name can be personalized in that case.

[Cynthia_sas]

Hi:
In this instance, with sheet_name='page', you cannot change the sheet names. However, you can do it if you use sheet_name='bygroup'. I changed the code below to use BY REGION, because that made more sense than BY PAGEVAR.
Cynthia

[deblee73]

HI, Cynthia.  This code worked for me (exporting each customer to a different worksheet) but I need one additional thing.  I need to add the same 2 title rows to the top of each worksheet.  I can  only get it to export for the first tab 

BANKRUPTCY CASE
FILED
CUSTOMER	KEY_DATE
1	07/28/2023

[ballardw]

---

@deblee73 wrote:

HI, Cynthia.  This code worked for me (exporting each customer to a different worksheet) but I need one additional thing.  I need to add the same 2 title rows to the top of each worksheet.  I can  only get it to export for the first tab 

BANKRUPTCY CASE
FILED
CUSTOMER	KEY_DATE
1	07/28/2023

---

You should:

1) create a new thread

2) as a minimum include the code you are currently using, better to include data as a data step

3) reference this thread as a "similar to"

[Cynthia_sas]

Thanks @ballardw, I was about to ask the same thing. Adding the 2 header rows to the top of each table should be possible with PROC REPORT and isn't related to the original question on sheet_interval. It might be related to sheet_interval, but I don't think it is. Partly it depends on the code being used, which we don't see here and partly it depends on the ODS statements which we don't see in this question.
cynthia