BookmarkSubscribeRSS Feed
PCHAVES
Calcite | Level 5

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;
7 REPLIES 7
snoopy369
Barite | Level 11

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

snoopy369
Barite | Level 11
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
PCHAVES
Calcite | Level 5

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

 

 

Reeza
Super User

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

 

 


 

snoopy369
Barite | Level 11

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.

Reeza
Super User

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

Reeza
Super User

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

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
  • 7 replies
  • 1600 views
  • 0 likes
  • 3 in conversation