topic Re: ods excell multiple sheets with tables horizontaly in ODS and Base Reporting

ods excell multiple sheets with tables horizontaly

xThylx — Tue, 02 Jan 2018 20:44:49 GMT

Hi,

I'm trying to create a report containing multiple sheets and with tables side by side in one sheet. From what i read so far, best way is to use tagsets because normally ods excell will overwrite the sheet where i try to put tables horizontally. Tagsets on the other hand have problems with multiple sheets, so i need to make the sheets in html format and then link them as sources of sheets(like in the code below)?

Is this the best way to do this?

I managed to do it in batch mode but when i try to open the excell file i get the message that the path to htmls is wrong.

How to set path to htmls so the excell can be just copied between systems?

ods msoffice2k path="C:\temp"(url=none) file="file1.html" newfile=output;
proc gchart data=sashelp.prdsale;
   pie product;
run;
quit;
proc print data=sashelp.prdsale;
run;
ods msoffice2k close;

ods path(prepend) work.templat(update);                                                                                                 
filename temp url "http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/msoffice2k_x.sas";                
%include temp;                                                                                                                          
ods tagsets.msoffice2k_x path="c:\temp" file="test.xls" style=statistical
    options(worksheet_source="c:\temp\file1.html,c:\temp\file2.html");

data _null_;
   file print;
   put _all_;
run;
ods tagsets.msoffice2k_x close; 
ods tagsets.msoffice2k_x path="c:\temp" file="test.xls" style=statistical
      options( worksheet_source="Graph_ouput#c:\temp\file1.html,
                                 Table_outpu#c:\temp\file2.html" );
data _null_;
   file print;
   put _all_;
run;
ods tagsets.msoffice2k_x close;

Re: ods excell multiple sheets with tables horizontaly

snoopy369 — Tue, 02 Jan 2018 21:34:01 GMT

Maybe I don't understand what you're asking, but it looks like you're asking two different things...

ODS EXCEL is perfectly happy to do two sheets for you. It's also happy to do two things on one sheet.

Example:

ods excel file="c:\temp\file1.xlsx" options(sheet_interval="none");
proc gchart data=sashelp.prdsale;
   pie product;
run;
quit;
proc print data=sashelp.prdsale;
run;
ods excel options(sheet_interval="now");
proc print data=sashelp.class;
run;
ods excel close;

You're not doing two things on one sheet, though, so I don't see exactly what you want there - but it's very possible. It's not possible to perfectly position things, though, which is where it gets messy. I'm not sure you could get the chart and table on the first worksheet to be left to right instead of up and down, for example. For that you'd probably need something more complicated, or to use ODS LAYOUT which I don't think works with ODS EXCEL, unless they've fixed that or I'm mistaken. (See the documentation for ODS LAYOUT GRIDDED for example).

Re: ods excell multiple sheets with tables horizontaly

HB — Tue, 02 Jan 2018 21:36:51 GMT

I don't know if it will make a difference but should

      options( worksheet_source="Graph_ouput#c:\temp\file1.html,
                                 Table_outpu#c:\temp\file2.html" );

      options( worksheet_source="Graph_output#c:\temp\file1.html,
                                 Table_output#c:\temp\file2.html" );

with ouput and outpu corrected to output?

Re: ods excell multiple sheets with tables horizontaly

Reeza — Wed, 03 Jan 2018 02:57:19 GMT

TAGSETS is fairly generic term and there are several tagsets in SAS, primarily EXCELXP.

You aren't creating an Excel file, you're creating HTML files that are linked but if that's what you want :).

ODS EXCEL can do a portion of what you want, but not side by side graphs.

You can control where the output starts.

It's not stated but your code includes a graph but no side by side tables?

If your requirements are includes graphs, multiple sheets AND side by side graphs then your best bet is the approach you've taken.

If you don't need graphs then I would recommend an entirely different approach since you're not showing side by side tables that I can see. You don't seem to be using any of the other tagset features besides the multi sheet, in that case using either an PROC EXPORT or the macro here maybe more useful.

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Note that you only need to run this session once and then never again.

filename temp url "http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/msoffice2k_x.sas";                
%include temp;

If you don't need side by side tables, but do need graphs and tables and multi-sheet then I would say use ODS EXCEL.

@xThylx wrote:

Hi,

I'm trying to create a report containing multiple sheets and with tables side by side in one sheet. From what i read so far, best way is to use tagsets because normally ods excell will overwrite the sheet where i try to put tables horizontally. Tagsets on the other hand have problems with multiple sheets, so i need to make the sheets in html format and then link them as sources of sheets(like in the code below)?

Is this the best way to do this?

I managed to do it in batch mode but when i try to open the excell file i get the message that the path to htmls is wrong.

How to set path to htmls so the excell can be just copied between systems?
ods msoffice2k path="C:\temp"(url=none) file="file1.html" newfile=output;
proc gchart data=sashelp.prdsale;
   pie product;
run;
quit;
proc print data=sashelp.prdsale;
run;
ods msoffice2k close;

ods path(prepend) work.templat(update);                                                                                                 
filename temp url "http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/msoffice2k_x.sas";                
%include temp;                                                                                                                          
ods tagsets.msoffice2k_x path="c:\temp" file="test.xls" style=statistical
    options(worksheet_source="c:\temp\file1.html,c:\temp\file2.html");

data _null_;
   file print;
   put _all_;
run;
ods tagsets.msoffice2k_x close; 
ods tagsets.msoffice2k_x path="c:\temp" file="test.xls" style=statistical
      options( worksheet_source="Graph_ouput#c:\temp\file1.html,
                                 Table_outpu#c:\temp\file2.html" );
data _null_;
   file print;
   put _all_;
run;
ods tagsets.msoffice2k_x close; 

Re: ods excell multiple sheets with tables horizontaly

ss171 — Fri, 23 Jul 2021 09:07:05 GMT

how can we place two or more tables in one excel sheet
tables to be placed horizontally one after the other i the same sheet using ODS excelxp?

Re: ods excell multiple sheets with tables horizontaly

Reeza — Fri, 23 Jul 2021 14:43:17 GMT

Only ODS HTML as far as I know.