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.
You could use 'Column Length' in dictionary.columns to make a style CELLWIDTH=xxxx .
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.
You could use 'Column Length' in dictionary.columns to make a style CELLWIDTH=xxxx .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.