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

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

View solution in original post

4 REPLIES 4
ballardw
Super User

@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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 4 replies
  • 1894 views
  • 0 likes
  • 2 in conversation