sas ods excel cannot produce multiple worksheet

Question

I'm trying to produce the below two worksheet "woe_con_out" and "woe_cat_out" into the same excel "woe_summary.xlsx". but after running this code, there is only one worksheet is generated (woe_cat_out). Where did I do wrong??

data translate;
format report $256.;
infile "out_con.out" dlm='09'x dsd;
input report $;
run;

ods excel file="woe_summary.xlsx" style = printer options(sheet_name = "woe_con_out") ;
proc print data = translate noobs
style(data) = {font_face = "courier_new" font_size = 11pt}
style(column) = {width = 1000%};
run;
ods excel close;

data translate;
format report $256.;
infile "out_cat.out" dlm='09'x dsd;
input report $;
run;

ods excel file="woe_summary.xlsx" style = printer options(sheet_name = "woe_cat_out") ;
proc print data = translate noobs
style(data) = {font_face = "courier_new" font_size = 11pt}
style(column) = {width = 1000%};
run;
ods excel close;

What version of SAS do you have? (9.4, I assume, but TS1M#?) — Joe

kstats9pt3 kstats9pt3 · Accepted Answer · 2017-03-14T18:03:52

You need ods excel options statements for EACH proc print statement:

/* first ods excel statement includes the file and style */
ods excel file="C:\desktop\woe_summary.xlsx" style = printer;

/* include ods excel options sheet_name for each PROC PRINT statement */
ods excel options(sheet_name = "woe_con_out");
proc print data = sashelp.class noobs;
var _all_;
run;

/* same as above -- include ods excel options sheet_name for each PROC PRINT statement */
ods excel options(sheet_name = "woe_cat_out"); 
proc print data = sashelp.fish noobs;
var _all_;
run;

/* close your ods excel engine */
ods excel close;

Also, in your situation, I would create two translate datasets, i.e. translate_con and translate_cat so you don't overwrite them and be able to use the method described.

sas ods excel cannot produce multiple worksheet

1 Answers