The SAS Output Delivery System and reporting techniques

Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

Reply
Highlighted
New Contributor
Posts: 2

Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

Hi,

 

I am using Base SAS 9,2.  I've tried to run the following two basic examples using tagsets.excelxp and tagsets.msoffice2k_x in order to get two or more excel tables side by side in the same sheet, but they do not work. Instead of writing the tables in the same sheet, they generate one file for each table.

 

Does anybody know the solution for this issue ?

 

Thank you.

 

Example of code using  TAGSETS.MSOFFICE2K_X :

-------------------------------------------------------------------------

ods tagsets.msoffice2k_x  file="c:\temp\panels.xls"
      options(panelcols="3") style=normal;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="CANADA";
  title "Canada Sales";
  run;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="U.S.A.";
  title "USA Sales";
  run;

  proc print data=sashelp.prdsale(obs=10);
  var actual predict country region;
  where country="GERMANY";
  title "Germany Sales";
  run;

  ods tagsets.msoffice2k_x  options(panelcols="2") ;

 proc print data=sashelp.prdsale(obs=20);
 where region="EAST";
 title "East Sales";
 run;

 proc print data=sashelp.prdsale(obs=20);
 where region="WEST";
 title "West Sales";
 run;


 ods tagsets.msoffice2k_x close;


Example of code using TAGSETS.EXCELXP:
------------------------------------

ods tagsets.ExcelXP 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 tagsets.ExcelXP close;
Super Contributor
Posts: 311

Re: Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

[ Edited ]

Do you require them side by side (so one columns A-F one columns G-L say), or is above/below okay?

If above/below is okay, then EXCELXP should do it, unless you have an old version (as you have an ancient version of SAS, you may also of EXCELXP); it has a "sheet_interval=none" option as you notice, and that does work as you posted in your question (except you have a spurious "ODS EXCEL" line there that you need to fix with "ODS TAGSETS.EXCELXP"). I just ran it using the most current version of the tagset and it worked fine; the chart printed and below it printed the first table, then a new sheet the second table.

 

MSOFFICE2K_X should do what you're thinking, the way you wrote it - I pasted the same exact code into my EG and just ran it, and it worked fine, produced a 3 column and a 2 column output.  But that's in 9.4.

If that doesn't work in 9.2, then I don't think you can easily do side-by-side in 9.2. You can do so in HTML (and then read into excel) in later versions whenever ODS LAYOUT became useful; it's possible that's in 9.2 but I think it wasn't good then, at least.

You also could use PROC MSCHART in 9.4, which specifically allows doing this very thing (except you actually produce an _excel_ chart instead of a SAS chart, but that's usually a good thing).

Super Contributor
Posts: 311

Re: Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

Posted in reply to snoopy369
I just remembered I had access to an old copy of 9.2, and indeed MSOFFICE2K_X seems to do what you want. Maybe make sure you have the most recent version of that tagset also?

https://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html
New Contributor
Posts: 2

Re: Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

Posted in reply to snoopy369

Thank you for bringing the spurious ods excel to my attention, but it did not work after fixing, anyway.

 

I forgot to mention that I've got the attached excel error message, about the file format.Excel error.png

 

 

Super User
Posts: 20,680

Re: Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

SAS 9.2 will not support ODS EXCEL, its about a decade too old. ODS EXCEL is production as of SAS 9.4 TS1M3 AFAIK.

 


PCHAVES wrote:

Thank you for bringing the spurious ods excel to my attention, but it did not work after fixing, anyway.

 

I forgot to mention that I've got the attached excel error message, about the file format.Excel error.png

 

 


 

Super Contributor
Posts: 311

Re: Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

[ Edited ]

The error message is related to the fact that neither of those products is actually making an XLSX file, so Excel thinks you might be doing something improper.  You're better off changing the extension to .xml; Excel can still open it correctly.

 

I ran the exact code you did in my 9.2 environment and had no issues whatsoever.  I strongly suggest you check that you have the most recent tagset, and if you don't, update it.  If you still are having issues, I am not sure I can explain it, as it works fine for me; you might want to check with SAS Support.

Super User
Posts: 20,680

Re: Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

Only ODS EXCEL generates native Excel files. 

The other tagsets generate either XML or HTML that Excel can interpret.

 

You can find the latest version of the tagset on the same page you found the example code in your first post. 

https://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html

Super User
Posts: 20,680

Re: Multiple tables in the same worksheet not working with tagsets.excelxp and tagsets.msoffice2k_x

1. Check to ensure that you're using the latest version of the tagsets.

2. Only MSOFFICE can do the side by side tables that I'm aware of, as well as ODS HTML. Both generate files that are not native Excel files. 

 

If you're only doing PROC PRINT I would suggest this macro that allows you to export your data to specified ranges. You'll need to do some calcs to pre-determine the ranges but that shouldn't be too difficult.

 

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

Ask a Question
Discussion stats
  • 7 replies
  • 183 views
  • 0 likes
  • 3 in conversation