The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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

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.


Accepted Solutions
Solution
‎03-07-2017 03:04 AM
Valued Guide
Posts: 505

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

/* 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 solution in original post


All Replies
Solution
‎03-07-2017 03:04 AM
Valued Guide
Posts: 505

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

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


Super User
Posts: 9,856

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

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 .

Occasional Contributor
Posts: 8

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

Thank you ,i got it

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 440 views
  • 0 likes
  • 3 in conversation