Hi,
I want to generate four reports in singal excel sheet side by side (i.e two side and down two reports side by side).
please tell how to generate this format and find attach excel file for report format.
/* 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");
');
/* 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");
');
Hi all,
How to get below macro values.
%utlfkil
%utl_submit_r64
WARNING: Apparent invocation of macro UTLFKIL not resolved.
WARNING: Apparent invocation of macro UTL_SUBMIT_R64 not resolved.
Thanks,
SS
There is an option START_AT= in ODS EXCEL ,
Check this.
http://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/
or you could resort to ODS MSOFFICE2K .
Thank you ,i got it
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.