topic Re: Any way to dynamically autofit width of cells so that data isn't chopped off? in ODS and Base Reporting

Any way to dynamically autofit width of cells so that data isn't chopped off?

tylerjazz1729 — Mon, 07 Oct 2019 18:13:57 GMT

I'm working on a reporting macro which takes a SAS data set as input and spits it out to excel so that it looks pretty and is formatted nicely. I've got everything where I want it except that the columns aren't wide enough for several fields (long names, addresses, etc). I know that this can easily be remedited with width= but since I'm doing it as a macro for any data set is there any way to auto fit the width of each column based on the length of the data in the input data set?

Relevant SAS Code:

/* create "column" statement on the fly */
proc sql noprint;
   select strip(name)
   into :column_names separated by " "
   from dictionary.columns
   where upcase(memname) = upcase("&data_set");
quit;

/* create "define" statements on the fly */
proc sql noprint;
   select cat("define ", name, " / display ", '"', strip(coalesce(label, name)), '" CENTER', "; ")
   into :definition_lines separated by " "
   from dictionary.columns
   where upcase(memname) = upcase("&data_set.");
quit;  

ods excel file = &out.
  options (row_heights = "55, 12, 0, 16, 0, 0, 0"
   sheet_interval = "PROC"
   orientation = "landscape"
   center_horizontal = "yes"
   scale = "80"
   embedded_titles = "yes"
   frozen_headers = "5"
   gridlines = "off"
   row_repeat = "1-4"
   sheet_name = &sheet.);

proc report data = &data_set. nowd missing center headline headskip
   style(header) = {font_weight = BOLD 
                             background=#418fde
                             foreground=white}
   style(column)={vjust=center
                           tagattr="wrap:no"
                           asis=on};

column &column_names.;

&definition_lines.

run;

Any help is appreciated, thank you.

Re: Any way to dynamically autofit width of cells so that data isn't chopped off?

ballardw — Mon, 07 Oct 2019 20:31:01 GMT

You can control individual column widths with the ODS EXCEL option ABSOLUTE_COLUMN_WIDTH

Generally specifying an appropriate format helps, You can also provide a WIDTH style element in the Define statements.

You have hidden your proc report definitions in a macro so we can't see what else may be affecting the column widths. It may help to share the actual text generated by all this. You can easily provide that by running the proc report code with the Option mprint turned on prior to execution. Copy from the log the Code generated and paste into a code box on the forum opened using the {I} or "running man" icon.

Re: Any way to dynamically autofit width of cells so that data isn't chopped off?

Ksharp — Tue, 08 Oct 2019 12:30:36 GMT

You could use 'Column Length' in dictionary.columns to make a style CELLWIDTH=xxxx .