BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mahesh_124
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
rogerjdeangelis
Barite | Level 11
/* 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

4 REPLIES 4
rogerjdeangelis
Barite | Level 11
/* 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");
');


sathya66
Barite | Level 11

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

Ksharp
Super User

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 .

Mahesh_124
Calcite | Level 5

Thank you ,i got it

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 3865 views
  • 0 likes
  • 4 in conversation