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

[Marta27]

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

I have tried "ods layout end" and "ods layout start" and this one continuous exporting in the same sheet, as well I tried closing ods and opening the same excel changin the name of the sheet but it replaced it.

Here I left what I wnat to export, it is mark  with a coment where I want to beging exporting in another sheet.

Thank you 😉

ods excel file = "C:\Prueba\exportar.xlsx" style=pearl	
		options (start_at="2,2" sheet_interval = 'page' sheet_name = "Prom");
		ods escapechar='~';
		ods text = "~S={font_size=14pt font_weight=bold foreground=blue font_face='Algerian' TEXTDECORATION=underline}~Capacidad Discriminante";
		ods text = "

		";
		ods text = "~S={font_size=11pt font_weight=bold}~    1. Tabla 1";
		ods text = "

		";
		*Tabla 1;
			proc report data=RWORK.Tabla1
				style(header)=[JUST = center FONT_FACE=Arial FONT_SIZE=10pt background = lightblue];
				column GINI REF DISC;
				define GINI/ 'Gini';
				define REF/ 'Referencia';
				define DISC/ 'Capacidad';
				define GINI / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
				define REF / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=120 ASIS=on};
				define DISC / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
			run;
		ods text = "

			";
		ods text = "~S={font_size=11pt font_weight=bold}~    2. Tabla 2";
		ods text = "

		";
		*Tabla 2;
			proc report data=RWORK.Tabla2
				style(header)=[JUST = center FONT_FACE=Arial FONT_SIZE=10pt background = lightblue];
				column Variables VI_Ref VI_Disc;
				define Variables/ 'Variables';
				define VI_Ref/ 'Referencia';
				define VI_Disc/ 'discriminante';
				define Variables / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
				define VI_Ref / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
				define VI_Disc / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
			run;
		ods text = "

		";
		ods text = "~S={font_size=14pt font_weight=bold foreground=blue font_face='Algerian' TEXTDECORATION=underline}~Estabilidad";
		ods text = "

		";
		ods text = "~S={font_size=11pt font_weight=bold}~    1. Tabla3";
		ods text = "

		";
		*Tabla 3;
			proc report data=RWORK.Tabla3
				style(header)=[JUST = center FONT_FACE=Arial FONT_SIZE=10pt background = lightblue];
				column Periodo P_Cuant P_Cual P_comportamental P_Final;
				define P_Cuant/ 'P. Cuantitativa';
				define P_Cual/ 'P. Cualitativa';
				define P_comportamental/ 'P. Comportamental';
				define P_Final/ 'P. Final';
				define Periodo / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
				define P_Cuant / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
				define P_Cual / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
				define P_comportamental / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
				define P_Final / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=200 ASIS=on};
			run;
		ods text = "

		";

		*______________________________________________________________________________________

		I WOULD LIKE TO CHANGE HERE OF SHEET

		 ______________________________________________________________________________________
		;
		ods text = "~S={font_size=11pt font_weight=bold}~    2. Tabla 4";
		ods text = "

		";
		*Tabla 4;
			proc report data=RWORK.Tabla4
				style(header)=[JUST = center FONT_FACE=Arial FONT_SIZE=10pt background = lightblue];
				column Ks_estabilidad;
				define Ks_estabilidad/ 'Kolmogorov-Smirov';
				define Ks_estabilidad / style(column) = {JUST=c FONT_FACE=Arial
						FONT_SIZE=10pt CELLWIDTH=250 ASIS=on};
			run;
	ods excel close;

[BrunoMueller · Accepted solution]

You can control the name and when to create a new worksheet using the options SHEET_INTERVAL=  and SHEET_NAME=, see below for an example. Check the doc on ODS EXCEL and all the options you can use.

ods excel file="c:\temp\sample.xlsx"
  options(
    sheet_interval= 'none'
    sheet_name="table1"
  )
;

proc print data=sashelp.class;
  where sex = "F";
run;


proc print data=sashelp.class;
  where sex = "F";
run;

ods excel
  options(
    sheet_interval= 'now'
    sheet_name="table2"
  )
;
proc print data=sashelp.cars;
run;
ods excel close;

[Marta27]

Ok, Thank you very much.

I have a problem with this one, after the line in which is indicate "sheet_interval = 'now'" it exports me the tittle of the table in one sheet and the table in other. ¿Do you know how can it be in the same sheet?

[BrunoMueller]

Yes I see, using ODS TEXT= does not behave as wanted.

As an alternative you can make use of the TITLE statement together with EMVEDDED_TITLES= option

title;
options nocenter;
ods excel file="c:\temp\sample.xlsx"
  options(
  sheet_interval= 'none'
  sheet_name="table1"
  embedded_titles= 'on'
/*  title_footnote_width='1'*/
  )
;
title "Listing for F";

proc print data=sashelp.class;
  where sex = "F";
run;

title  "Listing for M";

proc print data=sashelp.class;
  where sex = "M";
run;

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

title "Listing for Cars";

proc print data=sashelp.cars;
run;
title;
ods excel close;

[Marta27]

Thanks for your quick response. I have been trying "EMVEDDED_TITLES" and "EMBED_TITLES_ONCE" and none of them have worked for me. The tittles doesn't appear in the excel, using the "ods test" the test appear in a new sheet but with this other the test doesn't appear. I have tried the options = 'yes' asnd = 'on' without results. I would also like to know if it is possible to export more than one table on that second sheet.

Thank you very much 🙂

[BrunoMueller]

Does the sample code provided, work for you?
ODS TEXT and TITLE/FOOTNOTE are not the same.

[Marta27]

Yes, that same code works for me, but at the time I try to apply it to my code it doesn't work. Could it be because I'am using "proc report" instead of "proc print"? I want to use proc report because I want to give format to my tables (giving colors to the headers, change the names of the colums, writting a vector across intead of up to down....etc). With your code I have tried to export 2 tables in the second sheet, I couldn't, Is there any way to do it?

Thank you very much 🙂

[BrunoMueller]

Proc PRINT or Proc REPORT should not make any difference.

Please use the TITLE statement instead of the ODS TEXT.

To insert some text just before a table you may also use this syntax:

title "Listing for F";

proc report data=sashelp.class
  style(report) = { pretext="Text before the table" }
;
  where sex = "F";
run;

[Marta27]

Ok, thank you very much, now it works for me. Do you know if it is possible to export 2 tables in the second sheet?

[BrunoMueller]

you use the same basic code for the second sheet, as was used in the example on the first sheet

[Marta27]

I don't think I understand what you mean, I tried first to write:

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

and it export me each table in one sheet after that sentence is written. I also tried writting this:

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

     ods excel

        options ( sheet_interval = 'none' sheet_name = "sheet2"  embedded_titles= 'on' );

I don't know if you were refering to any of this ones but if it isn't, Would you mind explaining your way, please?

Thank you 😉