topic SAS9.4M4 ODS Excel problem multiple sheets in ODS and Base Reporting

SAS9.4M4 ODS Excel problem multiple sheets

JCool42 — Thu, 31 May 2018 18:07:18 GMT

We have an existing job running via Unix batch that utilizes ODS Excel. Prior to our conversion to SAS 9.4M4 the following code put the proc report output and the proc gplot on the same tab. We create multiple tabs (ALL, MC, PL) Prior to the conversion, the following code did this perfectly, now all the data is in one tab. I have reviewed all the OPTIONS for ODS Excel and I can find a reason for this. Appreciate any help you can provide. Additionally, when this code runs in SAS EG, multiple tabs are created. When we run via batch submit, we get one tab only

ods excel file="filename..xls" style=normal;

ods noproctitle;

%macro doit(sheetname);　

ods excel options(embedded_titles='yes'

sheet_interval="none"

sheet_name = "&sheetname"

absolute_column_width='10,10,5');

proc tabulate data=all_months out=auths;

class disposition reason rar_transaction_date;

var authorizations;

table disposition*reason*(n='#'*f=comma12. colpctn='%'*f=5.2) (all='Total'*f=comma12.),

authorizations * rar_transaction_date=' ';

run;

ods graphics / height=500 width=2000 ;

ods listing gpath="%sysfunc(getoption(work))";

GOPTIONS device=ACTXIMG;

proc sgplot data=graph1;

title "Unit Rate";

vline rar_transaction_date / response=unit_rate ;

XAXIS label = 'Date';

YAXIS label = 'Unit Rate' GRID min=0;

run;

%doit(All,);

%doit(MC);

%doit(PL);

Re: SAS9.4M4 ODS Excel problem multiple sheets

Haris — Thu, 31 May 2018 19:37:27 GMT

Shot in the dark here but I found that OPTION ValidVarName defaults are different between Base SAS and SEG. SAS uses 'V7' which replaces spaces and non-SAS characters with underscores versus 'ANY' in SEG with puts the name in quotes and adds an 'n' to the end. For example, 'Variable Name' would be read as Variable_Name by base SAS but 'Variable Name'n by SEG. I suspect that your culprit is in general SAS options rather than ODS Excel options.

Re: SAS9.4M4 ODS Excel problem multiple sheets

Cynthia_sas — Thu, 31 May 2018 22:07:13 GMT

Also, and this is minor, but could cause issues. When you use ODS EXCEL, the valid file extension is .XLSX (not .xls as your show). You should be getting a Microsoft warning window with .XLS as the file extension. Also GOPTIONS should not have an impact on PROC SGPLOT -- you should put your imagefmt= option in the ODS GRAPHICS statement.

cynthia