BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Marta27
Obsidian | Level 7

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;
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

@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:
creates a new worksheet.
 
Art, CEO, AnalystFinder.com
 

View solution in original post

14 REPLIES 14
Ksharp
Super User

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Opal | Level 21

@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:
creates a new worksheet.
 
Art, CEO, AnalystFinder.com
 
DocSteve
Obsidian | Level 7

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

Marta27
Obsidian | Level 7

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

Marta27
Obsidian | Level 7
@art297: Do you know if there is a way to obtain what I want in in SAS 9.4M3?
art297
Opal | Level 21

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

 

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
  • 14 replies
  • 1882 views
  • 3 likes
  • 4 in conversation