ODS Excel - Formatting font for an entire sheet

[SASaholic629]

When I open Excel, the default font is Calibri, font size 11. However, when I use ODS to create a report, it makes the entire sheet Arial, size 10. I don't want it to do that. I want to keep the entire sheet as Calibri, size 11.

I am able to make the table Calibri, size 11, but everything outside of that is still Arial, size 10. It looks silly. Why is SAS doing this? Is there a way to prevent it from defaulting to Arial, size 10 for the entire sheet? I just want to leave it as my default Excel settings.

Side note: PROC EXPORT doesn't mess with the Excel defaults. It remains as Calibri, size 11. But then my table looks boring, so that doesn't really help me either. My point with this is that SAS leaves the Excel default settings alone for PROC EXPORT but not through ODS. Why is that?

[Cynthia_sas]

Hi:
Just because you can use a font with Microsoft products does not mean that SAS (or ODS) know about or can use the font to create output. One scenario where you might have issues, is when you are running SAS and creating files on a server (such a Unix/Linux) server that does not have the same fonts as on a Windows system and so, if the font doesn't live on the operating system where the file is created, you won't see that font when you transfer and/or open the created file on Windows. Usually, if SAS can't find a font it will substitute on of the FreeType fonts like Albany AMT or Cumberland or "standard" fonts like Courier, Times New Roman or Helvetica. Have you checked your SAS Registry to see what fonts are available to you to use? If not, you can use REGEDIT or the FONTREG procedure to see which fonts are registered with SAS on the system where you are creating your output. Here's the documentation: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/graphref/n0840ghr9b9y3bn0zphem54hvgbt.htm and then, here's the doc on registering fonts: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/uprint/p1fl8dfzc4za2pn1e3eu0s43cjut.htm .
My memory is that when you install SAS on a Windows system, fonts installed by Microsoft are made available. But if any fonts are installed by Microsoft or by you after SAS is installed, than any new fonts have to be registered with SAS using PROC FONTREG. Otherwise, if you continue to have issues, you'll need to work with Tech Support.
Cynthia

[SASaholic629]

Thanks for your reply, Cynthia. I might not have been clear about what I want to happen. I don't think it's an issue of whether or not SAS has the font available to use. I'm able to make the individual table created by PROC REPORT to be in Calibri. And when I use ODS Excel to send it to Excel, the table remains in Calibri.

My problem is that the surrounding cells, the one SAS is not creating any data for, switches to Arial. I don't want the surrounding cells to change at all. I want them to remain in Calibri (which I know is available since it creates my table in Calibri). Just as they would if I were to do a PROC EXPORT, the entire sheet would remain in Calibri. However for some reason, ODS Excel changes the entire sheet to Arial, and then changes the tables created by the PROC REPORT to whatever font I tell it, in this case, Calibri.

I want the entire sheet to remain in Calibri. Not just the cells created by PROC REPORT. I know SAS can allow the entire sheet to remain in Calibri through PROC EXPORT. I want it to behave the same way using ODS Excel.

[Tom]

I suspect what you are talking about is the "fontscheme" of theme that Excel puts into the XLSX file.  If you create an XLSX file using EXCEL and then use a ZIP extractor to pull out the individual files that are the contents of an XLSX file you can see that EXCEL has added some theme settings into the file "xl/theme/theme1.xml" that is part of the XLSX file.

I am not sure if SAS has any options to modify that.

You could try to figure out if you could replace that yourself.  Perhaps there is some EXCEL menu item that lets you select the THEME to use?  Or perhaps you can just edit the XML file and update the XLSX file with the new version.

Or probably a better solution would be to leave the SAS generated cells alone and if you need to use some of the output as part of a report you want to hand craft in Excel then just reference the values so you can format your reporting sheet in the modified XLSX file in anyway you want.

[Cynthia_sas]

Hi:
I understand. Unfortunately, ODS only creates font specifications for the cells in the output it creates, so cells that fall outside the boundary of the SAS report are going to be in whatever default Excel sets up. SAS and ODS don't have any control over cells that they don't create.
Cynthia