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;
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;
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;
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?
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;
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 🙂
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 🙂
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;
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?
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 😉
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.