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

Accepted Solutions
BrunoMueller
SAS Super FREQ

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;

View solution in original post

10 REPLIES 10
BrunoMueller
SAS Super FREQ

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

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
SAS Super FREQ

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

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
SAS Super FREQ
Does the sample code provided, work for you?
ODS TEXT and TITLE/FOOTNOTE are not the same.
Marta27
Obsidian | Level 7

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
SAS Super FREQ

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

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
SAS Super FREQ
you use the same basic code for the second sheet, as was used in the example on the first sheet
Marta27
Obsidian | Level 7

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 😉

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 10 replies
  • 1289 views
  • 3 likes
  • 2 in conversation