ODS Excel - formats and text wrapping

thart — Thu, 06 Sep 2018 22:45:56 GMT

Hi, I am trying to use the ODS Excel destination with SAS 9.4M5, and I am getting a couple of odd results:

When inserting numbers from a proc report, the excel file has a strange format - ########0. Where a number is missing, ODS Excel seemingly inserts 9 spaces to match the format. I can change the format successfully either using tagattr or sas formats, however it will still replace a missing with spaces for each space in the format (for example, if I set # as the format, it will enter one space)
I have to use the ODS Excel option flow="tables" to prevent line breaks being entered in the table, however this automatically sets the format Wrap Text to on, and I have no way of turning it off. I have tried tagattr=wrap:no as suggested in many other locations, however this does not work (my guess is that the flow may be set after and overwriting the tagattr.

Has anyone came across these issues and how were they overcome?

ods _all_ close;
ods results off;

ods excel 
	style=DATA_REPORTING
	options(sheet_name = "Data Dictionary" 
		orientation = "Portrait"
		row_heights = "0,0"
		flow = "tables"
		frozen_headers = "Yes" 
		frozen_rowheaders = "Yes" 
		autofilter = "All" 
		index = "No" 
		);

proc report data = tpl.datadictionary split = "^"
	style(header column) = [font_size=10pt]
	style(header) = [verticalalign=middle tagattr='wraptext:yes']
	style(column) = [verticalalign=top tagattr='wraptext:no'];
	column Frame_element Description Data_type Explanation___Required_Codes___R Further_Information;
	define Frame_element / display style(column)=[width=4.00in] "Field Name";
	define Description / display style(column)=[width=4.00in] "Description";
	define Data_type / display style(column)=[width=0.75in] "Data Type";
	define Explanation___Required_Codes___R / display style(column)=[width=4.00in] "Explanation/Required Codes/Range";
	define Further_Information / display style(column)=[width=2.00in] "Further Information";
	compute Frame_element;
		count+1;
		if mod(count,2) then
			call define(_row_, "style", "style=[backgroundcolor=#DCE6F1]");
	endcomp;
run;	

ods excel close;

Re: ODS Excel - formats and text wrapping

Cynthia_sas — Fri, 07 Sep 2018 00:31:52 GMT

Hi:
Without data to test, it's hard to replicate your experience or make constructive suggestions. You seem to indicate that you're using a number format with tagattr, but I don't see it anywhere here in your code. You might want to consider working with Tech Support on this question, since they could look at all your code AND your data and help you come to a resolution.

cynthia

topic Re: ODS Excel - formats and text wrapping in ODS and Base Reporting

ODS Excel - formats and text wrapping

Re: ODS Excel - formats and text wrapping