Reducing column width with PROC REPORT exported vial Excel Tagset

svh — Tue, 06 Mar 2018 15:58:35 GMT

I am trying to produce a multiple-sheet workbook that shows the total number of bachelors, masters, and doctoral degrees for different degree classifications. For some reason, in the code example below, the CATEGORY and FIELD variables have extremely wide cells in the excel workbook, and I cannot find out how to reduce the size. I've attached a file (with no data) that shows the header widths. I've tried using the absolute_column_width option, but it produces no changes in the spreadsheet. Thanks in advance.

ODS TAGSETS.EXCELXP PATH='C:/Desktop' FILE="NEWDATA.XML" STYLE=statistical;
ods tagsets.ExcelXP options(orientation='Portrait'
							frozen_headers='yes'
							row_repeat='1-3'
							row_repeat='2'
							autofilter='2-3'
							fittopage='no'
							absolute_column_width='none'
							pages_fitwidth='1'
							pages_fitheight='100'
							width_fudge='0.8'
							sheet_interval='Bygroup'
							missing=' '
							);

PROC REPORT DATA=HAVE nowindows split='*';
	by year;
	COLUMN YEAR CATEGORY FIELD TOTAL_BACHELORS TOTAL_MASTERS TOTAL_DOCTORATES;
	DEFINE YEAR / display ;
	DEFINE CATEGORY / display ;
	DEFINE FIELD / display 'Field of Study' left style={just=l} ;
	DEFINE TOTAL_BACHELORS / display;
	DEFINE TOTAL_MASTERS / display;
	DEFINE TOTAL_DOCTORATES / display;
	BREAK AFTER YEAR/SUMMARIZE ;
	RUN; 
TITLE;
ods tagsets.ExcelXP close;
ods html;

Re: Reducing column width with PROC REPORT exported vial Excel Tagset

Cynthia_sas — Tue, 06 Mar 2018 16:05:57 GMT

Hi, typically, you use a STYLE(COLUMN)= override. so instead of what you have (which is not really the way to code, I would recommend that you use the STYLE(COLUMN) and STYLE(HEADER) method of performing style overrides and use the WIDTH= attribute.

As an example (changing colors too):

	DEFINE CATEGORY / display
             style(column)={width=1.5in} ;
	DEFINE FIELD / display 'Field of Study' left 
             style(header)={just=l background=lightyellow} 
             style(column)={just=l width=1.5in background=peachpuff};

cynthia

topic Reducing column width with PROC REPORT exported vial Excel Tagset in ODS and Base Reporting

Reducing column width with PROC REPORT exported vial Excel Tagset

Re: Reducing column width with PROC REPORT exported vial Excel Tagset