The SAS Output Delivery System and reporting techniques

cutomize pagebreak in excelxp tagset?

Reply
Frequent Contributor
Frequent Contributor
Posts: 81

cutomize pagebreak in excelxp tagset?

Hi Dear
below is my test coding. I am trying to put pagebreak on every 50 rows. of my excel spread sheet . how do I do it? thanks for your help in advance

regards
Inp



ods tagsets.excelxp path='C:\PROJECTS' file='mytest.xls' style=statistical
options( DOC='HELP' );

proc sort data=sashelp.class out=class;
by age;
run;

proc print data=class;
run;
SAS Super FREQ
Posts: 8,743

Re: cutomize pagebreak in excelxp tagset?

Hi:
I see you are using DOC='HELP' in your code. As far as I know, that reveals all the controls at your disposal. A quick review of the SAS log, using DOC='HELP' suboption shows these suboptions related to printing:
[pre]
Pages_FitWidth: Default Value '1'
This value determines the number of pages to fit the worksheet across
when printing.

Pages_FitHeight: Default Value '1'
This value determines the number of pages down to fit the worksheet
when printing.

FitToPage: Default Value 'no'
Values: yes, no, on, off.
Fit to Page when printing.

Page_Order_Across: Default Value 'no'
Values: yes, no, on, off.
If set to yes, the worksheet page order will be set to print across,
then down.

Center_Vertical: Default Value 'no'
Values: yes, no, on, off.
This value controls vertical centering for printing

Center_Horizontal: Default Value 'no'
Values: yes, no, on, off.
This value controls horizontal centering for printing

Gridlines: Default Value 'no'
Values: yes, no, on, off.
This value turns on gridlines for printing.
[/pre]

This, to me, looks almost exactly like the Excel popup dialog box for setting printing options.

The question I always ask myself is how does Excel (without SAS being involved) allow you to insert a custom page break into a worksheet. When I open Excel, type in some numbers (over 20 rows) and then go back to row 5 to insert a page break, I find that I have to use an Excel pulldown menu from the ribbon (In Excel 2010, for example) and follow these steps:
Highlight row 5,
Go to the Page Layout Tab
Select the arrow under Breaks on the menu
Manually choose "Insert Page Break"
This puts an Excel Page Break line -between- row 4 and row 5 of my sheet

TAGSETS.EXCELXP does not have a way to "interact" with pulldown menus on the ribbon. You might be able to "reverse engineer" what impact the pulldown choice has if you saved your manually created file as an XML 2003 File and then looked at the file with Notepad to see the type of XML that was written. However, I believe that, IF the Excel 2003 XML does show some kind of page-breaking XML tags, it is my memory that there's some kind of section put at the end of the XML that deals with PageBreaks or RowBreaking. This means that your SAS procedure (such as PROC PRINT), has no good way to impact the XML put at the end of the WORKSHEET unless you
1) post process the generated XML or
2) modify the tagset template for TAGSETS.EXCELXP -- which does requre some significant understanding of PROC TEMPLATE (it is, for example, not a task that I would undertake lightly).

This might lead you to look for some other method of splitting your data into logical groups to make printing easier/better/more cosmetically acceptable. For example, if you used BY group processing:
[pre]
proc sort data=sashelp.class out=class;
by age;
run;

ods tagsets.excelxp file='c:\temp\mytest_by2.xls' style=statistical
options( DOC='HELP' );
proc print data=class;
by age;
run;
ods _all_ close;
[/pre]

...And then printed the entire WORKBOOK, you would get a separate page printed for every AGE. If you arbitrarily needed to have every 50 rows on a new page when printed, you could make an arbitrary (or fake) page break variable and then use that variable to make multiple sheets in one Workbook and then print the entire Workbook. (Shown in the modified program below.)

cynthia
[pre]
data class;
set sashelp.class;
fakegrp = ceil(_n_/3);
run;

proc sort data=class out=class;
by fakegrp;
run;

ods tagsets.excelxp file='c:\temp\mytest_by_fake2.xls' style=statistical
options( DOC='HELP' );

** will get 3 rows/obs on every sheet because of fakegrp=ceil(_n_/3);
** when you print the entire workbook, every page will have just 3 obs;
proc print data=class;
by fakegrp;
run;
ods _all_ close;
[/pre]
Ask a Question
Discussion stats
  • 1 reply
  • 387 views
  • 0 likes
  • 2 in conversation