The SAS Output Delivery System and reporting techniques

Reducing column width with PROC REPORT exported vial Excel Tagset

Accepted Solution Solved
Reply
Occasional Contributor svh
Occasional Contributor
Posts: 17
Accepted Solution

Reducing column width with PROC REPORT exported vial Excel Tagset

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;

Accepted Solutions
Solution
‎03-06-2018 11:18 AM
SAS Super FREQ
Posts: 9,371

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

[ Edited ]

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

View solution in original post


All Replies
Solution
‎03-06-2018 11:18 AM
SAS Super FREQ
Posts: 9,371

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

[ Edited ]

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 220 views
  • 0 likes
  • 2 in conversation