The SAS Output Delivery System and reporting techniques

Output to Excel, multiple procs on a sheet

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Output to Excel, multiple procs on a sheet

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. 


Accepted Solutions
Solution
‎10-20-2016 04:05 PM
Regular Contributor
Posts: 169

Re: Output to Excel, multiple procs on a sheet

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

View solution in original post


All Replies
Solution
‎10-20-2016 04:05 PM
Regular Contributor
Posts: 169

Re: Output to Excel, multiple procs on a sheet

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

Valued Guide
Posts: 505

Re: Output to Excel, multiple procs on a sheet

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

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 471 views
  • 0 likes
  • 3 in conversation