topic Create worksheets with different papersize settings using tagsets.excelxp in ODS and Base Reporting

Create worksheets with different papersize settings using tagsets.excelxp

ScottBass — Fri, 14 Nov 2014 00:19:32 GMT

Hi,

I note the previous discussion thread

I ran the below code in a SAS 9.3/Win7/Office 2007 environment (it should run fine on your system with little to no modification):

%let excel_xml=C:\Temp\Temp.xml;

%* ODS setup ;

ods _all_ close;

ods noresults;

ods noproctitle;

ods tagsets.excelxp

file="&excel_xml"

style=journal

options(

sheet_interval='none'

frozen_headers='yes'

autofilter='all'

orientation='landscape'

pages_fitwidth='1'

pages_fitheight='9999'

print_footer='Page Number: &P'

row_repeat='header'

);

%* Sheet 1 ;

options

missing=' '

papersize='A4'

orientation=landscape

;

ods tagsets.excelxp

options(

sheet_interval='none'

sheet_name='Detailed Records'

);

title "Detailed Records";

footnote;

%let data=sashelp.class;

proc print data=&data (obs=20) noobs label

style(header)={

just=left

font_face="Arial"

font_size=10pt

bordertopwidth=1

borderbottomwidth=1

borderleftwidth=1

borderrightwidth=1

}

style(data)={

font_face="Arial"

font_size=10pt

bordertopwidth=1

borderbottomwidth=1

borderleftwidth=1

borderrightwidth=1

}

split='*'

;

run;

%* Sheet 2 ;

options

missing=' '

papersize='A3'

orientation=landscape

;

ods tagsets.excelxp

options(

sheet_interval='none'

sheet_name='Police Report'

);

title "Police Report";

footnote;

%let data=sashelp.shoes;

proc print data=&data (obs=20) noobs label

style(header)={

just=left

font_face="Arial"

font_size=10pt

bordertopwidth=1

borderbottomwidth=1

borderleftwidth=1

borderrightwidth=1

}

style(data)={

font_face="Arial"

font_size=10pt

bordertopwidth=1

borderbottomwidth=1

borderleftwidth=1

borderrightwidth=1

}

split='*'

;

run;

ods _all_ close;

ods listing;

However, when I open the file in Excel, both worksheets have the page size = Letter (the default page size). I also opened the XML file in an editor and searched for "A4", "A3", and "Letter", without any hits.

Is this a SAS bug, or something I'm doing wrong? Is there an updated tagset I should download from the SAS support website?

Thanks,

Scott

Re: Create worksheets with different papersize settings using tagsets.excelxp

Cynthia_sas — Fri, 14 Nov 2014 02:00:31 GMT

Hi:

Tagsets.Excelxp has its own orientation= sub option. So I believe you need to use that. If you use options(Doc='Help') in your code you would see that the possible settings are (look in the SAS log) shown below.

cynthia

======= from SAS log

Orientation: Default Value 'Portrait'

Tells excel how to format the page when printing.

The only other value is 'landscape'.

...more sub-options...

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

Margins:
     Margins can be set two ways. With the system options or through styles.
     The system options win over the style settings. In the style, the margins
     must be set on the 'Body' style element.

Setting the margins with the options statement is the easiest.

     options topmargin=1in
             bottommargin=1in
             leftmargin=.5in
             rightmargin=.5in;

     As as style definition, the Body element might look like this. This
     approach is more reusable since each program that uses the style
     automatically gets the margins

     style Body from Body /
         topmargin=.5in
         leftmargin=.25in;

======== end of display in SAS log for doc='Help'

Looks to me like orientation for TAGSETS.EXCELXP can only be portrait or landscape, no matter what the system option setting is.

Re: Create worksheets with different papersize settings using tagsets.excelxp

ScottBass — Fri, 14 Nov 2014 03:26:48 GMT

Hi ,

The orientation is fine in my Excel XML file, although I'm happy to move that setting to the tagset sub-option if that would help. The problem is with the papersize, which is not being set. I checked the tagsets doc and didn't see a papersize sub-option.

If you submit my code, open the XML output in Excel, Print Preview, Page Setup, is the papersize set to A4 and A3 for the two worksheets respectively?

My actual Excel workbook will have many worksheets, some "wide" and some "narrow". It will be really helpful to the end user if I can set the papersize programmatically, so they can "just print" from Excel without having to fiddle with options.

Regards,

Scott

Re: Create worksheets with different papersize settings using tagsets.excelxp

Cynthia_sas — Fri, 14 Nov 2014 03:47:56 GMT

Hi:

It has always been my understanding that PAPERSIZE as a system option, only applied to "paged" destinations like ODS PRINTER and ODS PDF and that the option "hooked into" the way that SAS prints paged output. It may or may not work with ODS RTF. I wouldn't even expect it to work with TAGSETS.EXCELXP. I am not at a computer where I can readily run code. I can only post code that I've already written. However, Tech Support could run your test code or tell you whether the PAPERSIZE option will even work with ODS TAGSETS.EXCELXP.

cynthia

Re: Create worksheets with different papersize settings using tagsets.excelxp

ScottBass — Mon, 17 Nov 2014 02:48:11 GMT

Thanks . I didn't get that understanding from , which specifically addressed options papersize and tagsets.excelxp.

I'll open a track with SAS TS. Thanks for the help.

Re: Create worksheets with different papersize settings using tagsets.excelxp

Cynthia_sas — Mon, 17 Nov 2014 03:00:18 GMT

Scott:

I'd forgotten that post! That was probably either 9.2 or 9.3 and not 9.4. Opening a track with Tech Support does seem like the right thing to do! I just tried the code from that earlier post in 9.4 and as you can see I did get A4 as the papersize in my Excel 2013 using SAS 9.4 and the ExcelXP tagset version:

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above,

v1.129, 11/07/2011).

cynthia

Re: Create worksheets with different papersize settings using tagsets.excelxp

ScottBass — Mon, 17 Nov 2014 05:25:41 GMT

Thanks for all your help, both on this as well as all the other posts throughout the SAS Communities site. Very much appreciated