topic Re: Paper size and EXCELXP in ODS and Base Reporting

Paper size and EXCELXP

Olivier — Tue, 27 Nov 2007 14:13:02 GMT

Hi all.
I'm puzzled. The system option PAPERSIZE does not seem to be taken into account in the TAGSETS.EXCELXP destination. I looked for a similar option to add to the opening ODS statement, but did not found any.
When I submit this code [pre]
OPTION PAPERSIZE=A4 ;
ODS TAGSETS.EXCELXP FILE="c:\temp\size.xls" ;
PROC MEANS DATA = sashelp.class ;
VAR age ;
CLASS sex ;
RUN ;
ODS TAGSETS.EXCELXP CLOSE ;[/pre]
and check in Excel in the PRINTER SETUP menu, I find out that the paper size needed is American Letter (LT 8.5x11in).

Am I missing something, is there a missing / hidden option, or is my question just plain stupid ?

Thanks in advance,
Olivier

Re: Paper size and EXCELXP

Cynthia_sas — Tue, 27 Nov 2007 15:19:57 GMT

Hi, Olivier:
You'll have to check with Tech Support for sure, but I have always thought that those options belonged to /worked with ODS PRINTER family destinations, only -- sort of like PAPERSOURCE, DUPLEX etc are only ODS PRINTER options. I thought that PAPERSIZE and PAPERDEST, et al were for PRINTING from the LOG, the LISTING window or some ODS PRINTER destinations. (They belong to a category called ODSPRINT.)

Since ExcelXP is a MARKUP destination, those options would not have an effect on Excel -- since they are SAS options. If you can figure out what XML tags need to be specified so Excel will use different values, then you could change the ExcelXP tagset to use those values.

cynthia

Re: Paper size and EXCELXP

Olivier — Tue, 27 Nov 2007 21:16:56 GMT

Thanks Cynthia. Your comment seems to prove this is all normal stuff : stick to the Printer family if you want something ready-to-print, and otherwise prepare yourself to include a little extra work on your file if you want it to be printed in a special way.

I found out on a forum (http://www.tek-tips.com/viewthread.cfm?qid=1178234&page=1) some information about values of the PaperSizeIndex tag : value 1 (as written by ODS TAGSETS.EXCELXP) stands for LETTER, whereas 9 is for A4.
The complete list of values is[pre]
Paper Size Table
Index Paper type Paper size
----------------------------------------------------
0 Undefined
1 Letter 8 1/2" x 11"
2 Letter small 8 1/2" x 11"
3 Tabloid 11" x 17"
4 Ledger 17" x 11"
5 Legal 8 1/2" x 14"
6 Statement 5 1/2" x 8 1/2"
7 Executive 7 1/4" x 10 1/2"
8 A3 297mm x 420mm
9 A4 210mm x 297mm
10 A4 small 210mm x 297mm
11 A5 148mm x 210mm
12 B4 250mm x 354mm
13 B5 182mm x 257mm
14 Folio 8 1/2" x 13"
15 Quarto 215mm x 275mm
16 10" x 14"
17 11" x 17"
18 Note 8 1/2" x 11"
19 #9 Envelope 3 7/8" x 8 7/8"
20 #10 Envelope 4 1/8" x 9 1/2"
21 #11 Envelope 4 1/2" x 10 3/8"
22 #12 Envelope 4 3/4" x 11"
23 #14 Envelope 5" x 11 1/2"
24 C Sheet 17" x 22"
25 D Sheet 22" x 34"
26 E Sheet 34" x 44"
27 DL Envelope 110mm x 220mm
28 C5 Envelope 162mm x 229mm
29 C3 Envelope 324mm x 458mm
30 C4 Envelope 229mm x 324mm
31 C6 Envelope 114mm x 162mm
32 C65 Envelope 114mm x 229mm
33 B4 Envelope 250mm x 353mm
34 B5 Envelope 176mm x 250mm
35 B6 Envelope 125mm x 176mm
36 Italy Envelope 110mm x 230mm
37 Monarch Envelope 3 7/8" x 7 1/2"
38 6 3/4 Envelope 3 5/8" x 6 1/2"
39 US Standard Fanfold 14 7/8" x 11"
40 German Std. Fanfold 8 1/2" x 12"
41 German Legal Fanfold 8 1/2" x 13"
[/pre]
So I guess I must modify my Tagset by using Proc Template, adding some kind of option or other.

PS : downloading the latest set of tagsets, the PAPERSIZE option is now taken into account. My dear thanks to all the ODS people who thought about it.