Default Font in ODS Excel Empty Cells

[Davanden]

I have my local Excel set to default to Arial 10 for new workbooks.  My preferred style for ODS EXCEL output is JOURNAL2.  Whenever I send procedure output to a spreadsheet, the cells with content in them are Arial 10 with a reasonable alignment for the type of content.  However, all of the empty cells in the spreadsheet are set to Courier 8, left alignment.  This is irritating if I need to modify the spreadsheet after SAS is done with it.

Is SAS causing this?  Excel?  Is there something I can do on the SAS end to make sure that the entire worksheet is set to Arial 10 with general alignment? 

I am using SAS 9.4.TS Level 1M4 Windows version 1.0.17763.

Dav Vandenbroucke

[ballardw]

---

@Davanden wrote:

I have my local Excel set to default to Arial 10 for new workbooks.  My preferred style for ODS EXCEL output is JOURNAL2.  Whenever I send procedure output to a spreadsheet, the cells with content in them are Arial 10 with a reasonable alignment for the type of content.  However, all of the empty cells in the spreadsheet are set to Courier 8, left alignment.  This is irritating if I need to modify the spreadsheet after SAS is done with it.

Is SAS causing this?  Excel?  Is there something I can do on the SAS end to make sure that the entire worksheet is set to Arial 10 with general alignment? 

I am using SAS 9.4.TS Level 1M4 Windows version 1.0.17763.

Dav Vandenbroucke

---

It might help to show an example of how you create an Excel document using one of the SAS supplied data sets like SASHELP.CLASS that shows this behavior. Include a complete ODS statement with your typical options.

Also, by "of the empty cells in the spreadsheet are set to Courier 8, left alignment" do you mean the rows that typically appear between tables or titles and tables or in the body of output?

[Davanden]

I mean that every cell in the worksheet into which SAS did not put some kind of content is formatted as Courier 8, left aligned.  See the attached program and spreadsheet.

Dav Vandenbroucke

[ballardw · Accepted solution]

The style Journal2, at least on my system, inherits almost all of the properties of Journal. The fonts in Journal are defined this way:

(Proc template can list the definitions of styles)

define style Styles.Journal;
   parent = styles.default;
   style fonts /
      'docFont' = ("<sans-serif>, Helvetica, Helv",2)
      'headingFont' = ("<sans-serif>, Helvetica, Helv",2,italic)
      'headingEmphasisFont' = ("<sans-serif>, Helvetica, Helv",2,bold italic)
      'FixedFont' = ("<monospace>, Courier",2)
      'BatchFixedFont' = ("SAS Monospace, <monospace>, Courier, monospace",2)
      'FixedHeadingFont' = ("<monospace>, Courier",2,italic)
      'FixedStrongFont' = ("<monospace>, Courier",2,bold)
      'FixedEmphasisFont' = ("<sans-serif>, Helvetica, Helv",2,bold italic)
      'EmphasisFont' = ("<sans-serif>, Helvetica, Helv",2,italic)
      'StrongFont' = ("<sans-serif>, Helvetica, Helv",2,bold italic)
      'TitleFont' = ("<sans-serif>, Helvetica, Helv",2,bold italic)
      'TitleFont2' = ("<sans-serif>, Helvetica, Helv",2,bold italic)
      'SASTitleFont' = ("<sans-serif>, Helvetica, Helv",2,bold italic);

The number shown in all of those fonts is a relative size without any specific unit which are up to specific ODS destinations to use. And for Courier I believe the 2 is interpreted as 8pt by ODS Excel which you see.

If you don't want Courier or size then you can create your own style by overriding the elements you don't like and specifying a unit in the size.

This is done in the Journal1A

define style Styles.Journal1a;
   parent = styles.journal;
   class fonts /
      'docFont' = ("<MTsans-serif>, Albany AMT",10pt)
      'headingFont' = ("<MTsans-serif>, Albany AMT",11pt,bold)
      'headingEmphasisFont' = ("<MTsans-serif>, Albany AMT",11pt,bold italic)
      'FixedFont' = ("<MTmonospace>, Courier",9pt)
      'BatchFixedFont' = ("SAS Monospace, <MTmonospace>, Courier",7pt)
      'FixedHeadingFont' = ("<MTmonospace>, Courier",9pt,bold)
      'FixedStrongFont' = ("<MTmonospace>, Courier",9pt,bold)
      'FixedEmphasisFont' = ("<MTmonospace>, Courier",9pt)
      'EmphasisFont' = ("<MTsans-serif>, Albany AMT",10pt,italic)
      'StrongFont' = ("<MTsans-serif>, Albany AMT",10pt,bold)
      'TitleFont' = ("<MTsans-serif>, Albany AMT",13pt,bold)
      'TitleFont2' = ("<MTsans-serif>, Albany AMT",12pt,bold);

I am not sure which of the many output style elements is actually used to output the empty cells. I suspect that you may not be able to set "general" as an alignment and that any text style not explicitly set to Left, Right or Center (and possibly Decimal) will default to left for character data.

You might try replacing all of the Courier references to Arial and set your 10pt.

If this using one of the Fixed type font elements then setting the font to Arial may cause peculiar output if you actually want some of your output as monospace.

[Davanden]

Okay, so it's coming from the style.  That clears up the mystery.  I don't have any experience with modifying styles.  It appears that this is a skill I need to pick up!

Thanks for your help.

Dav Vandenbroucke