%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;
... View more