topic ODS Excel - make column width 'behave' like excel format / alignment / 'shrink to fit' in SAS Programming

ODS Excel - make column width 'behave' like excel format / alignment / 'shrink to fit'

Rampsas1 — Sun, 17 May 2020 22:22:15 GMT

Hello,

(using base SAS 9.4)

Utilizing ODS Excel and/or PROC REPORT, want to make column width 'behave' like excel format / alignment / 'shrink to fit.' Is this even possible? (see attached .xlsx)

dm 'log;clear';
/* DELETE FILE */
%let myfile=C:\ods_test.xlsx;
filename myfile "&myfile";
data _null_;
rc=fdelete("myfile");
run;

/* RAW DATA */
data raw;
var4='1234';        /*length=4  #of chars in header=4 */
var8='12345678';    /*length=8  #of chars in header=4 */ 
header_8='123';     /*length=3  #of chars in header=8 */
run;

ods noresults;
ods html close;

/* ODS EXCEL */
ods excel file="&myfile." 
style=styles.htmlblue options(
ABSOLUTE_COLUMN_WIDTH = "4,4,4" 
AUTOFILTER ="ALL"
FROZEN_HEADERS="ON"
SHEET_NAME='Data'
);
run;

/* PROC REPORT */
proc report data=raw  nowd  ;
column var4 var8 header_8; 
define var8 / style(column)=[width=75];        /* ?? 'shrink to fit' so that variable value of length 8 fits column width of 4 */
define header_8 / style(header)=[width=5000];  /* ?? 'shrink to fit' so that variable 'header' of length 8 chars fits column width of 4 */
run;

ods excel close;
ods html;

If not possible to 'shrink to fit', is it possible to find the 'longer of':

i) the given length of the SAS variable OR

ii) the width (# of chars) of the 'column header' ..

.. utilizing this code snippet as a base?

(this might conceivable allow all 'cell content' and/or 'column headers' to be visible in all circumstances).

proc sql noprint;
  select length into : length_var separated by ',' 
  from dictionary.columns
  where libname='WORK' and memname='RAW'
  order by varnum;
quit;

For my current business purpose, wrapping the text is not ideal but if anyone has observations about that as well, I'll take it.

many thanks for any insight

Re: ODS Excel - make column width 'behave' like excel format / alignment / 'shrink to fit'

ChrisNZ — Mon, 18 May 2020 02:04:25 GMT

1. HTML files normally "expands to fit" if columns are too narrow for the contents, and there is room in the page width. You may want to try that.

2. is it possible to find the 'longer of':

i) the given length of the SAS variable OR

ii) the width (# of chars) of the 'column header' ..

Yes you can: max(length(VAR)) will give you the figure you are seeking for i), while your code should give you ii) .

Re: ODS Excel - make column width 'behave' like excel format / alignment / 'shrink to fit'

Rampsas1 — Mon, 18 May 2020 18:45:18 GMT

Thank you for the reply ChrisNZ.

Your response (of course "max(length(VAR))" .. dah) did help to trigger a solution for using the larger of the variable name length (the excel column header) or the variable value length (the excel 'cell content').

This does allow - dynamically - the excel output to show the full column header when the variable length is smaller than it OR reduce the column width when the 'cell content' is shorter than the header..... so a reasonable solution (code below).

But, the ideal solution is still to somehow make the excel column width 'behave' the same as when you manually 'right click on entire column' / format / alignment / 'shrink to fit.' Does anyone know if this is doable ?? (result needs to be output to excel)

There are times when a string may be too long to reasonably display inside a cell and so it would be nice to 'shrink it to fit' a defined column width in lieu of having the column width expand to accommodate the long string. (using something like "define var12 / style(column)=[cellwidth=50]" in proc report just wraps the text within the cell and thus expands the cell height ...a result that is not workable.

many thanks again for any insight or solutions

/* RAW DATA */
data raw;
infile datalines;
input var8 : $8. var3 : $3. var12 : $12. name12_len_3 : $3. var6 : $6.; /* ABCDEFGHIJKL */
datalines ;
123456 123 12345678 123 123456
12345 1 1234567890 123 1
1234567 1 123456 12 2
;
run;

/* i)  the variable name length (what will be the excel column header) */
proc sql;
create table var_names as 
  select varnum, name 
  from dictionary.columns
  where libname='WORK' and memname='RAW'
  order by varnum;
quit ;
data name_lengths;
set var_names;
name_length=length(name);
run;

/* ii) the variable value length */
proc sql;
create table var_lengths as 
  select varnum, name, length as value_length
  from dictionary.columns
  where libname='WORK' and memname='RAW'
  order by varnum;
quit ;

/* result: the greater of i) or ii) */
data ds_lengths;
merge name_lengths var_lengths;
by varnum;
if name_length gt value_length then max_length=name_length;
else max_length=value_length;
drop name_length value_length;
run; 

/* place into macro variable */
proc sql noprint;
  select max_length into : length_mv separated by ','
  from ds_lengths; 
quit;
data _null_;
%put &=length_mv.;
run;

Re: ODS Excel - make column width 'behave' like excel format / alignment / 'shrink to fit'

ChrisNZ — Tue, 19 May 2020 04:10:12 GMT

> the ideal solution is still to somehow make the excel column width 'behave' the same as when you manually 'right click on entire column' / format / alignment / 'shrink to fit.'

Each reporting format has its own quirks.

Excel does not autoexpand like HTML does, and other formats don't mimic Excel either.

Here are some options you can use to format proc report (for example) and that are specific to Excel outputs:

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

Another way might be to predefine an Excel range with all the desired formatting and just load some data.

Re: ODS Excel - make column width 'behave' like excel format / alignment / 'shrink to fit'

Rampsas1 — Thu, 21 May 2020 12:35:34 GMT

Many thanks again for the replies and insight ChrisNZ. It is appreciated.
I marked this as 'solved' accepting the idea that we can't necessarily have SAS make the ODS Excel mimic the exact 'shrink to fit' behavior but we can utilize format and absolute_column_width to acquire a reasonable approximation.