Output to Excel, multiple procs on a sheet

[TBarney]

After reading much on the contect here as well as papers and notes in the support area and the SAS for Dummies Blog I am still stuck.  We have learned to make all of the sheets we need to support our requirements except for the content of one sheet.  This sheet needs to have summaries for two reagions side by side on a sheet.  All of the examples I have found so far show out put flowing down the sheet.

Example

                            Region 1                                                   Region 2

             percent of total by widget                          percent of total by widget

             Number of missing widgets by store         Number of missing widgets by store

and so on.

I have not seen an example where I can place my Freq/Tabulate/Report output in two columns like this. 

[paulkaefer · Accepted solution]

There are some suggestions on StackOverflow. This page discussed sending results to specific cells on a selected sheet. Does this help?

[rogerjdeangelis]

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