topic Exporting to Excel - Change default font in SAS Programming

Exporting to Excel - Change default font

Seabird — Mon, 22 Aug 2016 17:39:57 GMT

Is there a setting I can change in the Properties so that when I export my SAS results into Excel (simply using the Export button), it opens in Calibri font size 10 instead of Calibri font size 11?

Thank you.

Re: Exporting to Excel - Change default font

ballardw — Mon, 22 Aug 2016 18:48:57 GMT

Export creates data for interchange and as such provides no font information.

Options would be to set in Excel, possibly as your default, or to use another procedure, possibly a report and a method ODS Excel or ODS tagsets.excelxp that can provide font information.

Re: Exporting to Excel - Change default font

Seabird — Mon, 22 Aug 2016 18:57:52 GMT

Thank you, ballardw. I was afraid that might be the case, since I hadn't found the answer in the Community posts. My Excel is set up to create new files in my preferred font, so that wouldn't be the solution. If I get annoyed enough with having to manually change the font, I'll consider your ODS solutions.

Re: Exporting to Excel - Change default font

ballardw — Mon, 22 Aug 2016 19:05:40 GMT

Perhaps set up a Excel VB macro to select all cells and set the font. Then at least the process could be a single key stroke.

Of course one could also wonder about the concern over any font at all. Reports are the only thing I would spend any time on text appearance. If the "data" is supposed to be a report then one of the SAS Report procedures and ODS would likely be a better approach in general.

Re: Exporting to Excel - Change default font

Seabird — Mon, 22 Aug 2016 19:25:21 GMT

I obtain data from SAS then manipulate it in Excel, often comparing with data from other sources, and I simply prefer the smaller font size. Since the workbook created when exporting from SAS is set for a larger font, any other sheets added to the workbook will be in that font. Although it only takes a few seconds to adjust the workbook to the new font, I find it annoying and was hoping to find a quick solution in SAS.

Re: Exporting to Excel - Change default font

rogerjdeangelis — Tue, 30 Aug 2016 12:44:15 GMT

%let pgm=utl_excel_default_font;

HAVE CLASS DATASET

Up to 40 obs SD1.CLASS total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
  4    Carol       F      14     62.8      102.5
  5    Henry       M      14     63.5      102.5
  6    James       M      12     57.3       83.0

WANT

 1.  Workbook default font aria1 narrow and font_size=10
 2.  Start report at column 3 row 2

EXCEL EXCEL  EXCEL  EXCEL
ROW   COL 1  COL 2  COL 3

1                   NAME       SEX    AGE    HEIGHT    WEIGHT
2                    1    Alfred      M      14     69.0      112.5
3                    2    Alice       F      13     56.5       84.0
4                    3    Barbara     F      13     65.3       98.0
5                    4    Carol       F      14     62.8      102.5
6                    5    Henry       M      14     63.5      102.5


SOLUTION

* SAS data for excel;

data "d:/sd1/class.sas7bdat";
  set sashelp.class;
run;quit;

* the zip command was not in my path so I set it;

%utl_submit_r64(
library(openxlsx);
library(haven);
Sys.setenv('R_ZIPCMD' = 'd:/Rtools/bin/zip.exe');
class<-read_sas('d:/sd1/class.sas7bdat');
class;
wb <- createWorkbook();
addWorksheet(wb, 'S1');
modifyBaseFont(wb, fontSize = 10, fontName = 'Arial Narrow');
writeDataTable(wb, 'S1', x = class, startCol = 3, startRow=2);
saveWorkbook(wb, 'd:/xls/modifyBaseFontExample.xlsx', overwrite = TRUE);
);

* Output from R;

> library(openxlsx);
library(haven);
Sys.setenv('R_ZIPCMD' = 'd:/Rtools/bin/zip.exe');
class<-read_sas('d:/sd1/class.sas7bdat');
class;
wb <- createWorkbook(); addWorksheet(wb, 'S1');
modifyBaseFont(wb, fontSize = 10, fontName = 'Arial Narrow');
writeDataTable(wb, 'S1', x = class, startCol = 3, startRow=2);
saveWorkbook(wb, 'd:/xls/modifyBaseFontExample.xlsx', overwrite = TRUE);

      NAME SEX AGE HEIGHT WEIGHT
1   Alfred   M  14   69.0  112.5
2    Alice   F  13   56.5   84.0
3  Barbara   F  13   65.3   98.0
4    Carol   F  14   62.8  102.5
5    Henry   M  14   63.5  102.5



%macro utl_submit_R64(pgmx)/des="Semi colon separated set of R commands";
  * write the program to a temporary file;
  filename r_pgm temp lrecl=32766 recfm=v;
  data _null_;
    file r_pgm;
    pgm=compbl("&pgmx");
    put pgm;
    putlog pgm;
  run;
  %let __loc=%sysfunc(pathname(r_pgm));
  * pipe file through R;
  filename rut pipe "c:\Progra~1\R\R-3.2.4\bin\x64\R.exe --vanilla --quiet --no-save < &__loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
    putlog _infile_;
  run;
  filename rut clear;
  filename r_pgm clear;
%mend utl_submit_r64;