The SAS Output Delivery System and reporting techniques

ods excell multiple sheets with tables horizontaly

Reply
Senior User
Posts: 1

ods excell multiple sheets with tables horizontaly

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;

 

Super Contributor
Posts: 320

Re: ods excell multiple sheets with tables horizontaly

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).

Super Contributor
Super Contributor
Posts: 260

Re: ods excell multiple sheets with tables horizontaly

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" );

be

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

with ouput and outpu corrected to output?

Highlighted
Super User
Posts: 22,845

Re: ods excell multiple sheets with tables horizontaly

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 Smiley Happy

 

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;

 


 

 

Ask a Question
Discussion stats
  • 3 replies
  • 152 views
  • 0 likes
  • 4 in conversation