Generate multiple(side by side) reports in singal excel sheet

Mahesh_124 — Mon, 06 Mar 2017 13:40:28 GMT

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.

Re: Generate multiple(side by side) reports in singal excel sheet

rogerjdeangelis — Mon, 06 Mar 2017 14:32:19 GMT

/* 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");
');

Re: Generate multiple(side by side) reports in singal excel sheet

Ksharp — Tue, 07 Mar 2017 03:56:56 GMT

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 .

Re: Generate multiple(side by side) reports in singal excel sheet

Mahesh_124 — Tue, 07 Mar 2017 08:05:01 GMT

Thank you ,i got it

Re: Generate multiple(side by side) reports in singal excel sheet

sathya66 — Fri, 13 Apr 2018 08:27:48 GMT

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,

topic Re: Generate multiple(side by side) reports in singal excel sheet in ODS and Base Reporting

Generate multiple(side by side) reports in singal excel sheet

Re: Generate multiple(side by side) reports in singal excel sheet

Re: Generate multiple(side by side) reports in singal excel sheet

Re: Generate multiple(side by side) reports in singal excel sheet

Re: Generate multiple(side by side) reports in singal excel sheet