Maybe in 9.4M3 the start_at ODS option will work, but for now
you can sort of do it with SAS and R. Not all formatting is copied.
HAVE (TWO EXCEL SHEETS)
EXCEL A B C
ROW ---------|-----------|---------------
1 Country Product Actual Sales
2 CANADA BED $47,729.00
3 CHAIR $50,239.00
4 DESK $52,187.00
5 SOFA $50,135.00
6 TABLE $46,700.00
7 GERMANY BED $46,134.00
8 CHAIR $47,105.00
9 DESK $48,502.00
------
SHEET1
------
EXCEL A B C
ROW ---------|-----------|---------------
Predicted
1 Country Product Sales
2 CANADA BED $44,215.00
3 CHAIR $46,796.00
4 DESK $49,393.00
5 SOFA $45,726.00
6 TABLE $46,889.00
7 GERMANY BED $43,796.00
8 CHAIR $44,069.00
9 DESK $44,639.00
SOFA $49,517.00
------
SHEET2
------
WANT THE REPORTS TO BE SIDE BY SIDE IN SHEET3
(We could hvae added sheet1 to sheet2 but it
is better to create sheet3 and drop sheet1 and 2.
EXCEL A B C F G H
ROW ---------|-----------|--------------- ---------|-----------|---------------
Predicted
1 Country Product Actual Sales Country Product Sales
2 CANADA BED $47,729.00 CANADA BED $44,215.00
3 CHAIR $50,239.00 CHAIR $46,796.00
4 DESK $52,187.00 DESK $49,393.00
5 SOFA $50,135.00 SOFA $45,726.00
6 TABLE $46,700.00 TABLE $46,889.00
7 GERMANY BED $46,134.00 GERMANY BED $43,796.00
8 CHAIR $47,105.00 CHAIR $44,069.00
9 DESK $48,502.00 DESK $44,639.00
SOFA $49,517.00
SHEET1
SOLUTION
* CREATE TWO SAS REPORTS
%utlfkil(d:/xls/class1.xlsx);
ods excel file="d:/xls/class1.xlsx";
ods excel options(sheet_name="sheet1" start_at="A1");
proc report data=sashelp.prdsale;
column country product actual;
define country / group;
define product / group;
rbreak after / summarize;
run;quit;
* you cannot output to the same sheet - ignores sheet1 and puts the
report in sheet2 (9.4M2);
* I believe this is supposed to work in SAS 9,4M3?;
* Does not work in 9.4M2;
ods excel options(sheet_name="sheet1" start_at="A1");
proc report data=sashelp.prdsale ;
column country product predict;
define country / group;
define product / group;
rbreak after / summarize;
Run;
ods excel close;
* I believe this is supposed to work in SAS 9,4M3?;
* Does not work in 9,4M2;
HERE IS A R SOLUTION;
%utl_submit_r64('
library(XLConnect);
wb <- loadWorkbook("d:/xls/class1.xlsx");
createSheet ( wb , "sheet3" );
prcntg <- createCellStyle(wb);
setDataFormat(prcntg, format = "$00,000.00");
sheet1 = readWorksheet(wb, sheet = getSheets(wb)[1]);
sheet2 = readWorksheet(wb, sheet = getSheets(wb)[2]);
writeWorksheet(wb,sheet1,sheet="sheet3",startCol=1,header=T);
writeWorksheet(wb,sheet2,sheet="sheet3",startCol=6,header=T);
setCellStyle(wb, sheet = "sheet3", row = 1:17, col = 3, cellstyle = prcntg);
setCellStyle(wb, sheet = "sheet3", row = 1:17, col = 8, cellstyle = prcntg);
saveWorkbook(wb,"d:/xls/class1.xlsx");
');
1. load existing workbook
2. create formats for sales and predicted sales
3. load sheet1 and sheet2
4. Place sheet1 and sheet2 intoo sheet3
... View more
