BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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


got_a4_in_94.png

View solution in original post

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

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.

ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Cynthia_sas
SAS Super FREQ

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

ScottBass
Rhodochrosite | Level 12

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.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Cynthia_sas
SAS Super FREQ

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


got_a4_in_94.png
ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2102 views
  • 0 likes
  • 2 in conversation