ODS EXCEL Output to Multiple worksheets (same workbook) AND insert two (or more) tables SIDE-BY-SIDE

[Zeus_Olympous]

Hi,

I am running a macro, using the "fresh" ODS EXCEL to output two tables on the same worksheet and this is getting repeated by th macro for several datasets.

ODS EXCEL outputs the pair of tables just fine across different worksheets (of the same workbook) just fine.

The first table up the second under.

Now I need to place the tables not vertically but horizontally, side-by-side.

In the previous version

ods tagsets.msoffice2k_x

an option PANCOLS= n  could be used to solve the problem.

Unfortunately I fail to find a smilar option for the new ODS EXCEL.

Any advise would be more than welcome!

Thank you.

[Ksharp]

Try 

• START_AT= option

http://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

[Zeus_Olympous]

Dear Ksarp,

My code is attached.

Actually I want the 2nd print output

"   proc print data=LIB_IMP.&dn (obs=10);
run;"

to be placed side-by-side to the first output say at K5 cell of the worksheet.

For that purpose I added "ods excel options(sheet_name="%scan(&dn,1,_)" start_at="K5" );" just before the 2nd proc printbut nothing happened.

Then I read this link

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

Experimenting with ODS EXCEL to create spreadsheets from SAS - The SAS Dummy

1. • Chris Hemedinger on February 25, 2015 11:26 am

     Peter, thanks for the comment. Currently, the START_AT option does not allow you to modify the "output position" in mid-sheet. I expect that what you are observing is ODS EXCEL respecting only the last START_AT option that you specify.

I can't figure out what I am doing wrong.

Thank you.

===================================================================================

ods excel file="c:\temp\multitablefinal.xlsx"
   options(sheet_interval="none" contents="yes" embedded_titles="yes" PAGE_ORDER_ACROSS= "ON" );

%macro sample1(dn, label=no);

    data &dn;
        set LIB_IMP.&dn;
        array change _numeric_;

        do over change;
            if change=0. then
                change=.;
        end;
    run;

    * formats to group observations;
    proc format;
        value nm . = '0' other = '1';
        value $ch ' ' = '0' other = '1';
    run;

    * PROC FREQ produces data set TABLES with counts of missing/non-missing values;
    ods listing close;
    ods exclude all;
    ods output onewayfreqs=tables;

    proc freq data=&dn;
        tables _all_ / missing;
        format _numeric_ nm. _character_ $ch.;

        /*title "&dn";*/
    run;

    ods output close;
    ods exclude none;
    ods listing;

    *place variable labels in a data set;
    proc contents data=&dn noprint
        out=l&dn (keep=name label format length rename=(name=var) index=(var));
    run;

    * create a macro variable &LABEL_EXIST ... value 1 if there are labels;
    data _null_;
        length check $5000;
        set l&dn end=last;
        check = cats(check,label);

        if last then
            call symputx('label_exist',(lengthn(check) gt 0));
    run;

    * create the missing data report as a data set;
    data r&dn;
        length var $32;

        do until (last.table);
            set tables;
            by table notsorted;
            array names(*) f_:;
            select (names(_n_));
                when ('0')
                    do;
                        miss = frequency;
                        p_miss = percent;
                    end;

                when ('1')
                    do;
                        ok = frequency;
                        p_ok = percent;
                    end;
            end;
        end;

        miss = coalesce(miss,0);
        ok = coalesce(ok,0);
        p_miss = coalesce(p_miss,0);
        p_ok = coalesce(p_ok,0);
        var = scan(table,-1);
        set l&dn key=var/unique;
        keep var label format length miss ok p_:;
        format miss ok comma7. p_: 5.1;
        label
            miss = 'N_MISSING'
            ok = 'N_OK'
            p_miss = '%_MISSING'
            p_ok = '%_OK'
            var = 'VARIABLE'
            label = 'LABEL'
        ;
    run;
***  EXPORT TO EXCEL ***;
    ods excel options(sheet_name="%scan(&dn,1,_)" );

    proc print data=r&dn label /* noobs*/;
        %if &label ne no and &label_exist ne 0 %then
            %do;
                id var label;
                var label format length  miss p_miss ok p_ok;    
                title "%scan(&dn,1,_)";
            %end;
        %else
            %do;
                id var;
                var label format length  miss p_miss ok p_ok;
                title "%scan(&dn,1,_)";
            %end;
    run;

ods excel options(sheet_name="%scan(&dn,1,_)" start_at="K5" );

   proc print data=LIB_IMP.&dn (obs=10);
run;


    /* A dummy table is created. */
    ods excel options(sheet_interval="output");
    ods exclude all;

    data _null_;
        dcl odsout obj();
    run;

    ods select all;
    ods excel options(sheet_interval="none");
%mend sample1;

proc contents data=LIB_IMP._all_ noprint out=contents;
run;

data _null_;
    set contents;
    by memname;

    if first.memname;

    /*  call execute(cats('title "memname";'));*/
    call execute(cats('%sample1(',memname,')'));
run;

ods excel close;

===================================================================================

[rogerjdeangelis]

/* T1002940 Output to Excel, multiple 'proc  reports' side by side on one sheet  ( R xlconnect)

see sheet3 in
https://dl.dropboxusercontent.com/u/15716238/class1.xlsx
* you can use R to drop sheet1 and sheet2

You should be able to paste the R code below into SAS 9.4M2 with 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.
ODS excel seems better than anything i r?Python or Perl for stylized excel reports.

* Maybe 9.4M3 supports 'ODS start at' on the same sheet? This would eliminate
the call to R.


see
https://goo.gl/T3HJte
https://communities.sas.com/t5/ODS-and-Base-Reporting/Generate-multiple-side-by-side-reports-in-singal-excel-sheet/m-p/338440/highlight/false#M18073

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
built into 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");
');