The SAS Output Delivery System and reporting techniques

ODS Excel creates a spreadsheet with all sheets selected

Reply
Occasional Learner
Posts: 1

ODS Excel creates a spreadsheet with all sheets selected

Hello,

 

I am having trouble with xlsx files created using ODS excel. Each file is generated with all worksheets selected (so if you open the workbook all sheets are highlighted as though they have been selected together, but if you pick one the others go back to standard grey). This is causing problems for the team which use my file next.

 

Is there a way of getting ODS excel to not output like this? Code below.

 

* >> EXCEL << ;
     
ods listing close;
ods _all_ close;
ods results off;

options missing=0;

ods Excel file = "&reportOutputPath\&ReportName..xlsx" options (index='OFF');

	* Sheet 1 : Summary ;
	ods Excel
	   options(sheet_name="Summary"
				sheet_interval='none'
	            zoom='100'
				absolute_column_width = "10,10,12,14,12,10"
	        	 );
	run;

	proc odstext;
	  p "&ReportName. Summary" / style=[color=black font_style=italic font_size=11pt font_weight=bold];
	run;

	proc report data = xs_summary_table ;
		column agency cover number ;
		define agency				/ display 'Agency';
		define cover				/ display 'Cover';
		define number				/ display 'Count';
	run;

	title;
	footnote;

	* Dummy worksheet ;
	ods excel options(sheet_interval="table");
	ods exclude all;
		data _null_;
		file print;
		put _all_;
		run;
	ods select all;

	* Sheet 2 : Successes ;
	ods Excel
	   options(sheet_name="Successes"
				sheet_interval='none'
	            zoom='100'
				absolute_column_width = "14,14,14,15.5,14,18,14,14,14,16,14,14,14,14,14,14,16.5,14,14,14,20,20,20,20,20,20,20"
	        	 );
	run;

	proc odstext;
	  p "Successes" / style=[color=black font_style=italic font_size=11pt font_weight=bold];
	run;

	proc report data = good ;
		column agency cover event account_number expected actual ;
		define agency				/ display 'Agency';
		define cover				/ display 'Cover';
		define event				/ display 'Event';
		define account_number		/ display 'Account Number';
		define expected				/ display 'Expected Result';
		define actual				/ display 'Actual Result';
	run;

	title;
	footnote;

ods excel close;
Frequent Contributor
Posts: 92

Re: ODS Excel creates a spreadsheet with all sheets selected

It sounds like the worksheets are grouped.  See the ungroup xlsx worksheets thread for a link to a hot fix. 

Ask a Question
Discussion stats
  • 1 reply
  • 138 views
  • 0 likes
  • 2 in conversation