topic ODS Excel controlling column width in ODS and Base Reporting

ODS Excel controlling column width

SuryaKiran — Fri, 28 Sep 2018 19:35:40 GMT

I came across two issues when using ODS EXCEL with PROC ODSTEXT

1) why absolute_column_width doesn't apply in the below cases.

2) Long text in Proc odstext is not displayed properly.

When I tested text with length ~143 is displayed without warping, but longer text is being hidden.

ods excel file="test1.xlsx" 
options(absolute_column_width="100" ) style=Excel;
ods text="SAS was developed at North Carolina State University";
ods text="Wikipedia: SAS (previously 'Statistical Analysis System') is a software suite developed by SAS Institute for advanced analytics, multivariate analyses, business intelligence, data management, and predictive analytics.";
ods excel close;


ods excel file="test2.xlsx" 
options(absolute_column_width="100" ) style=Excel;
proc odstext;
p "SAS was developed at North Carolina State University";
p "Wikipedia: SAS (previously 'Statistical Analysis System') is a software suite developed by SAS Institute for advanced analytics, multivariate analyses, business intelligence, data management, and predictive analytics.";
run;
ods excel close;

Re: ODS Excel controlling column width

SuzanneDorinski — Fri, 28 Sep 2018 20:12:11 GMT

I don't know why this trick works, but it does. Set width=1000% for the really long text.

%let path=/folders/myfolders/ODS Excel examples;

ods excel file="&path/test2.xlsx" style=Excel;

proc odstext;
	p "SAS was developed at North Carolina State University";
	p "Wikipedia: SAS (previously 'Statistical Analysis System') is a software suite developed by SAS Institute for advanced analytics, multivariate analyses, business intelligence, data management, and predictive analytics." 
		/ style={width=1000%};
run;

ods excel close;

The screen shot below shows what test2.xlsx looks like for me.

Set width=1000% on really long text in PROC ODSTEXT to force it to display

Re: ODS Excel controlling column width

SuryaKiran — Mon, 01 Oct 2018 13:42:59 GMT

This is weird, I will open SAS Technical Support track and see if they come up with something.

Re: ODS Excel controlling column width

SuryaKiran — Thu, 04 Oct 2018 16:59:03 GMT

Using width and tagattr= (merge cells) can better control the the text flow.

style={width=3in tagattr='mergeacross:5'}

ods excel file="test2.xlsx"
style=Excel;
proc odstext;
p "SAS was developed at North Carolina State University";
p "Wikipedia: SAS (previously 'Statistical Analysis System') is a software suite developed by SAS Institute for advanced analytics, multivariate analyses, business intelligence, data management, and predictive analytics." / style={width=3in tagattr='mergeacross:5'};
run;
ods excel close;

The ABSOLUTE_COLUMN_WIDTH= option is for the columns within the table. The TEXT= option is simply floating text which is a good use of PROC ODSTEXT.