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
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
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.
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
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
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.
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
Thanks for all your help, both on this as well as all the other posts throughout the SAS Communities site. Very much appreciated
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.