topic Re: ODS RTF columns too wide in ODS and Base Reporting

ODS RTF columns too wide

Chang — Fri, 05 Oct 2018 10:34:24 GMT

hi,

My SAS code works well when exporting compact tables via ODS RTF and PROC REPORT. However, the exported table can have columns too wide when there are just a few columns in the table. I've tried to change the linesize (OPTIONS LD=80) and cellwidth in my individual DEFINE statements (e.g. DEFINE variable / style(column)={cellwidth= 1cm} ...) Unfortunately, all this setting has no effect on my exported table, which has columns too wide leaving large white space between columns:

I had to save the RTF as a docx and manually adjust the column width:

But it would be great to be able to do this, either reducing the table size or column width, in SAS. I hate to mouse clicking in Microsoft Word.

Here is my SAS macro for writing individual DEFINE statement:

%macro def_group(	cVar		/*character variable*/
									,option					/*option= order : duplicated rows shown as blank, SAS will reorder your data */
																/*option= group : duplicated rows shown as blank, SAS doesn't reorder your data */
									,cName				/*name of the character variable*/	
									,isFmt=N				/*apply a format to the variable or not*/	 
									,cFmt=					/*which format*/	
									,cWide=25pt		/*cell width*/
									,headerAlign=center 	/*alignment of header text in a column: left, center, right*/
									,colAlign=right 			/*alignment of content in a column: left, center, right, d (decimal point) */
									,marginRight=0
									,background=white 		/*color of background in a column*/
									,foreground=black		/*color of foreground (i.e. font color) in a column*/
									);

	define	&cVar.	/	&option. "&cName."  order=data 
		%if &isFmt.=Y %then 
			%do;
				format= &cFmt.
				/*style(column)={just=left cellwidth= &cWide.}*/
				style(header)={just=&headerAlign.}
				style(column)={just=&colAlign. cellwidth= &cWide. rightmargin=&marginRight. background=&background. foreground=&foreground.};	/*replace order with &option. in the future*/
			%end;
		%else
			%do;
				style(header)={just=&headerAlign.}
				/*style(column)={just=left cellwidth= &cWide.}*/ /*replace order with &option. in the future*/
				style(column)={just=&colAlign. cellwidth= &cWide. rightmargin=&marginRight. background=&background. foreground=&foreground.};
			%end;
	
%mend def_group;

and this is how the macro is called:

%def_group(	cVar=target_phenotype,	option=group, cName=Target phenotype,	 cWide=3 cm, headerAlign=left,colAlign=left);

Here are my system options which are set before ODS RTF is run:

options	missing = ' '  	/*Specifies the character to print for missing numeric values.*/ 
			center 			/*Specifies whether to center or left align SAS procedure output*/
			nodate 			/*suppress default print of data time that this SAS program executed*/
			nonumber 	/*suppress default print of page number on first title line of each page*/
			orientation=portrait 	/*orientation=landscape*/
			LS= 80 /*each line of output to contain no more than 200 characters ( LS stands for "linesize")*/
		;
TITLE1; TITLE2;
/*------------------------------------------------------changing table margins------------------------------------------------------*/
options	bottommargin = 	0.5 cm 
	   		topmargin = 			0.5 cm 
	   		rightmargin = 			0.5 cm /*0.5in */
	   		leftmargin = 			0.5 cm /*0.5in */
			; 

/*suppress the warning that a quoted string that exceeds 262 characters in length*/
options NOQUOTELENMAX ; 

/* first tell ODS what character will be used as the ODS ESCAPECHAR character value*/
ods escapechar='~';
ods escapechar='^';

If the layout is more adjustable in other format (e.g. ODS PDF) than RTF, I will be happy to learn that format.

Re: ODS RTF columns too wide

Ksharp — Fri, 05 Oct 2018 11:03:59 GMT

Did you try

style(column)={cellwidth= 5%}

proc report data=sashelp.class nowd style={outputwidth=10%};
run;

Re: ODS RTF columns too wide

Chang — Fri, 05 Oct 2018 11:53:59 GMT

hi Keshan,

I've just tried to add style(column) {cellwidth= 5%} in my DEFINE statements or in the options of my PROC REPORT. Neither changed the layout of my exported table. I am still seeing the same wide columns. I guess users have less control on the layout when exporting the table as an external file than the result in SAS output window.

Re: ODS RTF columns too wide

ballardw — Fri, 05 Oct 2018 15:29:17 GMT

@Chang wrote:

hi Keshan,

I've just tried to add style(column) {cellwidth= 5%} in my DEFINE statements or in the options of my PROC REPORT. Neither changed the layout of my exported table. I am still seeing the same wide columns. I guess users have less control on the layout when exporting the table as an external file than the result in SAS output window.

You should include the ODS destination code and the entire Proc Report code in your example code. There are possible interactions between proc level options and define settings and we can't see both.

Also the "macro" you show only affects character variables (says so in the definition) but your output appears to contain a number of numeric columns that are also "wider" in the output than you apparently want.

Did you have a working example before attempting to create the macro(s) that may be involved?

Example data or using a common SAS supplied data set such as SASHELP.CLASS also helps.

Re: ODS RTF columns too wide

Ksharp — Sat, 06 Oct 2018 10:04:32 GMT

Also try

options leftmargin=20cm rightmargin=20cm;

Re: ODS RTF columns too wide

Cynthia_sas — Sat, 06 Oct 2018 15:45:28 GMT

Hi:

I always think it's a bad idea to "over-control" the column widths and give them a percent unless you are absolutely sure about the margins for the output and the number of columns in the output. I prefer to use style(report) and change width on the PROC REPORT statement like this:

Example 1 and 2: Margins .5in all around, orientation=portrait -- only vary WIDTH=

Example 3 and 4: Margins 1.5 in all around, orientation=portrait -- only vary WIDTH=

There are only a few places in PROC REPORT that the simple STYLE= with a (location) is used -- mostly in BREAK, RBREAK and COMPUTE statements. All the rest of the time, you should always specify STYLE(REPORT), STYLE(HEADER), STYLE(COLUMN), STYLE(SUMMARY), STYLE(LINES) if you are putting overrides in the PROC REPORT or DEFINE statements (define supports HEADER and COLUMN).

The original complaint was that the TARGET_PHENOTYPE column was too wide. Since no data was posted, I just used SASHELP.CLASS again. But if I needed to control the width of one column, I would not use percents and I would not combine a DEFINE width with a REPORT width change. I would do something like this:

Example 5: Control all widths in DEFINE

Before you macro-ize anything, you should be sure that you have code (for all permutations) that creates the output you want. If you started with code that worked and then it didn't work correctly after using macro code, it is possible that your macro logic put the wrong value in the wrong place or you had a combination of arguments that were not accounted for in the macro logic.

Testing without macro, it seems that WIDTH=, when used in either the PROC REPORT statement or the DEFINE statement, does permit control of column widths. My general advice is to avoid percents unless you want to control the overall width of the whole report and let PROC REPORT divide the columns into that width. Otherwise, control column width with DEFINE.

Hope this helps,

Cynthia

Re: ODS RTF columns too wide

Ksharp — Sun, 07 Oct 2018 10:11:31 GMT

@Cynthia_sas

What is different between OUTPUTWIDTH= and WIDTH= ?

Doesn't WIDTH= only work under LISTING destination ? When it work for RTF destination ?

Re: ODS RTF columns too wide

Cynthia_sas — Sun, 07 Oct 2018 13:53:52 GMT

HI:

As shown in my code, WIDTH= in a STYLE override is OK to use in ODS Destinations that support STYLEs. If you use:
style(location)={width=99in} or style(location)={outputwidth=99in}
then WIDTH and OUTPUTWIDTH are aliases.

Outside of a STYLE override, the simple WIDTH= in a DEFINE statement does just control LISTING. So if you had this:

define charvar / 'Label' width=20

style(column)={width=.5in color=red};

Then the width=20 is a LISTING only option, but inside the STYLE(COLUMN) option, the width=.5in is an ODS option. And, as you can see in my code, works for ODS RTF, PDF, HTML, and ODS TAGSETS.EXCELXP and ODS EXCEL.

If you look at the code in my screenshots, WIDTH= is always inside a STYLE= override, not as a simple DEFINE statement option. WIDTH=, by itself, in a DEFINE statement is a LISTING only option, but in a STYLE override, either WIDTH= or OUTPUTWIDTH= can be specified as aliases for each other.

It makes sense, in a STYLE override, OUTPUTWIDTH was the first name for the attribute, when you could just adjust the width of the entire report in the PROC REPORT statement. But in later versions of SAS, when you could adjust the WIDTH of columns in a STYLE override, it made sense for them to be aliases to ODS.

Cynthia

Re: ODS RTF columns too wide

Ksharp — Mon, 08 Oct 2018 12:22:40 GMT

OK. I understand something.

But what is difference between WIDTH= and CELLWIDTH= ?

Re: ODS RTF columns too wide

Cynthia_sas — Mon, 08 Oct 2018 13:27:42 GMT

Hi: When used in the context of a DEFINE statement with a STYLE= override, CELLWIDTH= and WIDTH= are aliases. So in a PROC REPORT statement OUTPUTWIDTH and WIDTH are aliases for the same attribute; in a DEFINE statement CELLWIDTH and WIDTH are aliases for the same attribute.

From the doc:

Cynthia

Re: ODS RTF columns too wide

DonZ — Tue, 14 Mar 2023 22:17:02 GMT

Hi, Cynthia:
Regarding inside Style(column) = {...} override, are Cellwidth =, Width =, Outputwidth= all aliaes?

Best Regards

Don

Re: ODS RTF columns too wide

Cynthia_sas — Wed, 15 Mar 2023 00:47:20 GMT

Hi:

Easy enough to test. This is quite an old post, but I think that the answer is still yes. Have you tried something that did not work? If so, you may want to open a track with Tech support so they can test on the same version that you're using.

Cynthia

Re: ODS RTF columns too wide

DonZ — Wed, 15 Mar 2023 16:33:33 GMT

Hi, Cynthia:

Thanks for your feedback.

Yes, as i tested, inside Define NAME / Style(column) = {...}, WIDTH = /CELLWIDTH=/OUTPUTWIDTH = all have achieved exactly similar layout results.

Amazingly, I could observe they impact both Listing and RTF in different ways simultaneously.

Best

Don

define age / 'Age' width = 50 /*no unit of in*/
style(column) = [cellwidth = 1.00in just = c];

Re: ODS RTF columns too wide

Cynthia_sas — Wed, 15 Mar 2023 20:41:14 GMT

Hi: Basically the older width=50 is a LISTING only option that is ignored by all ODS destinations and the STYLE override syntax is respected by most ODS destinations, but ignored by the LISTING window.
Cynthia