How can I export several tables in 2 differents sheets of an excel document with ods (2 in each)?

[Marta27]

I am trying to export several tables to an excel sheet in a concrete format with ods. I want to export 2 tables in one sheet and 2 tables in a second sheet without changing of document, but I am unable to do it with ods.

I have tried the option (sheet_interval = "now") but it export me every single table after it in one sheet and I want 2 tables in that second sheet.

Here I left what I want to export.

Thank you 😉

title;
options nocenter;
ods excel file="c:\temp\sample.xlsx"
  options(  sheet_interval= 'none'  sheet_name="tables1_2"  embedded_titles= 'on'  );

	title "Table 1";
		proc report data=RWORK.Table1
			style(header)=[JUST = center FONT_FACE=Arial FONT_SIZE=10pt background = lightblue];
			column col1 col2 ;
			define col1/ 'Column 1';
			define col2/ 'Column 2';
			define col1 / style(column) = {JUST=c FONT_FACE=Arial
					FONT_SIZE=10pt ASIS=on};
			define col2 / style(column) = {JUST=c FONT_FACE=Arial
					FONT_SIZE=10pt ASIS=on};
		run;

	title  "Table 2";
		proc report data=RWORK.Table2
				style(header)=[JUST = center FONT_FACE=Arial FONT_SIZE=10pt background = lightblue];
				column col1 col2 ;
				define col1/ 'Column 1';
				define col2/ 'Column 2';
				define col1 / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt ASIS=on};
				define col2 / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt ASIS=on};
			run;

ods excel
  options( sheet_interval= 'now'  sheet_name="tables3_4"  );

	title "Table 3";
		proc report data=RWORK.Table3
			style(header)=[JUST = center FONT_FACE=Arial FONT_SIZE=10pt background = lightblue];
			column col1 col2 ;
			define col1/ 'Column 1';
			define col2/ 'Column 2';
			define col1 / style(column) = {JUST=c FONT_FACE=Arial
					FONT_SIZE=10pt ASIS=on};
			define col2 / style(column) = {JUST=c FONT_FACE=Arial
					FONT_SIZE=10pt ASIS=on};
		run;

	title "Table 4";
		proc report data=RWORK.Table4
			style(header)=[JUST = center FONT_FACE=Arial FONT_SIZE=10pt background = lightblue];
			column col1 col2 ;
			define col1/ 'Column 1';
			define col2/ 'Column 2';
			define col1 / style(column) = {JUST=c FONT_FACE=Arial
					FONT_SIZE=10pt ASIS=on};
			define col2 / style(column) = {JUST=c FONT_FACE=Arial
					FONT_SIZE=10pt ASIS=on};
		run;

title;
ods excel close;

[Ksharp]

Try add some code in the middle of code.

FONT_SIZE=10pt ASIS=on}; run;

ods excel options(sheet_interval='proc');

ods select none;

proc print data=sashelp.class;

run;

ods select all;

ods excel options( sheet_interval= 'now' sheet_name="tables3_4" );

title "Table 3";

proc report data=RWORK.Table3

[Marta27]

What should I obtain writing that code in the middle? That code doesn't make difference in my result. Was it suppose to print me another table?

Thank you 😉

[DocSteve]

Hi Marta27, 

If I understand you correctly, you want Proc Report output for datasets 1 and 2 on the first worksheet and for datasets 3 and 4 on the second worksheet?  That is exactly what I just got when I roughed-out four data files as input and ran your program, colors, font, everything.

I am running 9.4, TS1 M5

Steve

[Marta27]

Hi Steve,

I am using SAS 9.4 TS1M3, and that's exactly what I want. But, when I run my code I obtain datasets 1 and 2 on the first worksheet, dataset 3 on the second worksheet and dataset 4 on the third worksheet. I don't know how to obtain them just in 2 sheets.

Thank you , 😉

[DocSteve]

Hi Marta 27, 

The versions of SAS are essentially identical, yet we get different results (mine being the ones you want), so something else is different besides the capabilities of the SAS Software (assuming no major changes between M3 and M5).

First, be sure that the example you posted is the exactly the same as what you ran (I made no changes except to change the libref for the input datasets).  My datasets are probably much different from yours, but given the output that should be no issue.

Second, I cannot imagine anything in MS-Excel that would fail here, but what are the names of your worksheets?  You want one worksheet named "tables1_2" and a second one named "tables3_4" and you are getting a third one, too.

Steve

[Marta27]

Hi Steve,

Yes I am using exactly the same code I posted, the names of the sheets are also the posted, the third one that's appear it is called "tables3_4 2". I don't really understand why we obtain different results with the same code. I have even copy the code I posted and changed just the datasets when I read your first message, just in case I had written something different.

Thank you,

Marta

[DocSteve]

That leaves me at a loss. 

For some reason, you seem to be getting a "sheet_interval='now' " after the third Proc Report (keeping the same sheet name, so Excel is giving you the suffix "2" to the name).  There is nothing in the code that I see could cause that.

It really -- REALLY -- should not be because Excel is seeing the sheet as filled (max number of rows is over a million; columns go out to something like xva, over 16,000).  That is why I believe you are actually getting the command through SAS.

Just so you can see what is happening here, I am attaching the Program -- 99+% yours -- and Excel output.

Steve

[Marta27]

Hi Steve,

Thank you very much! Your code is exactly as mine and you obtain what I want although it doesn't work for me.

My tables are of 2 rows and 4 columns so I don't think the excel limits is a problem.

As Art297 said probably are the versions.

Than you

Marta

[art297 · Accepted solution]

@Marta27: My guess is it is due to the version you're using (i.e., M3 rather than M5).

Your code includes sheet_interval='now' but, according to the documentation:

The ODS EXCEL statement was introduced in SAS 9.4M3.

In SAS 9.4M5, the ODS EXCEL statement supports the following options:

SHEET_INTERVAL= "NOW"

creates a new worksheet.

Art, CEO, AnalystFinder.com

[DocSteve]

Whoops!  Thanks for the catch.  My assumption above was quite wrong viz-a-viz M3 and M5.

[Marta27]

Thank you very much! I didn't get why it worked for Steve and not for me 😉

[Marta27]

@art297: Do you know if there is a way to obtain what I want in in SAS 9.4M3?

[art297]

If you're on a Window's-based system, you could use the macro you can find at: http://www.sascommunity.org/wiki/Excelling_to_Another_Level_with_SAS

However, to do what you want, you'd need to know the upper left cell location of the rectangle where you want the second file placed.

Art, CEO, AnalystFinder.com

[Marta27]

Thank you very much 😉