BookmarkSubscribeRSS Feed
xThylx
Calcite | Level 5

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;

 

5 REPLIES 5
snoopy369
Barite | Level 11

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

HB
Barite | Level 11 HB
Barite | Level 11

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?

Reeza
Super User

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;

 


 

 

ss171
Calcite | Level 5
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?
Reeza
Super User
Only ODS HTML as far as I know.

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
  • 5 replies
  • 2172 views
  • 0 likes
  • 5 in conversation