/* T0100040 Output to Excel, multiple procs on one sheet
see sheet3 in
https://dl.dropboxusercontent.com/u/15716238/class1.xlsx
* you can use R to drop sheet1 and sheet2
SAS 9.4M2 woth IML/R
Here is an solution for two reports side by side reports in one sheet
1. Create sheet1 and sheet2 with your reports using ods excel
2. Use R to combine the reports in sheet3
* note you can do it all in R but I think you want to use SAS to create the reports.
* Maybe 9.4M3 supports 'ODS start at' on the same sheet? This would eliminate
the call to R/
http://goo.gl/1r0OgO
https://communities.sas.com/t5/ODS-and-Base-Reporting/Output-to-Excel-multiple-procs-on-a-sheet/m-p/297849#M16852
1 create your reports using ods excel
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.
This is by no means perfect. If you have a template
for sheet3 this will use the template. I had to
create the USD format and apply it.
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 (in sheet1 and sheet2)
%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;
Use R to put them side by side;
%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");
');
... View more
