Does SAS allow different column widths and alignments in multiple tabs of the spreadsheet?

[ph8252]

Hi SAS experts,

I had go over many posts that discuss how to adjust column widths for excel outputs but I couldn't find anything that fits my needs (or I just miss it, please share if you find one!) 

#1) I am generating an excel output with multiple tabs and I want some of the columns to have their width autofit to the length of the text in the observations. For example, in column "location_type" I have only "office" in one tab and "Skilled nursing facility" in another tab. I want to adjust the widths to be only as wide as the text. I have tried absolute_column_width (see code) and this standardizes the column widths across tabs. Is there a way to do it?

#2) I currently have all my columns left-aligned (as default) but I want "location" and "Inv" to be center-aligned. Is there a way to specify alignments to some of the columns?

Please advise! Thank you for your help.

ODS EXCEL FILE="C:\Users\TestOB_&run..xlsx"
OPTIONS (Orientation = 'landscape' 
sheet_name = "OB List"
sheet_interval = 'Proc'
embedded_titles='yes' 
AUTOFILTER ="All"
FLOW="TABLES"
ABSOLUTE_COLUMN_WIDTH="5,11,8,14,12,58,59,5,25.5,15,12,12,12,12"
ROW_HEIGHTS="39, 18"); 
ods listing close;
Proc report data =extract2 nowd style(header)=[Backgroundcolor = white color=Black] style(report)=[just=center];
column OBs closure priority list_number date_created location location_type district Inv Ncv var1 var2 var3 var4;
compute closure;
 if closure = "Yes" then
     call define("closure", "style", "style=[backgroundcolor=orange]"); /*Highlight Closure = "Yes"*/
endcomp;
define OBs / computed;
compute obs;
 dsobs + 1;
 obs = dsobs;
 endcomp;
run;

%macro ByNcv;
*add loop to the ODS Code;
%do I=1 %to &extn ;
ODS EXCEL
OPTIONS (Orientation = 'landscape' 
sheet_name = "&&S&I"
sheet_interval = 'Proc'
embedded_titles='yes' 
AUTOFILTER ="All"
FLOW="TABLES"
ABSOLUTE_COLUMN_WIDTH="5,11,8,14,12,58,59,5,25.5,15,12,12,12,12"); 
ods listing close;
Proc report data =extract2 nowd style(header)=[Backgroundcolor = white color=Black] style(report)=[just=center];
	where Ncv = "&&S&I";
column OBs closure priority list_number date_created location location_type district Inv Ncv var1 var2 var3 var4;
define OBs / computed;
compute obs;
 dsobs + 1;
 obs = dsobs;
 endcomp;
compute closure;
 if closure = "Yes" then
     call define("closure", "style", "style=[backgroundcolor=orange]"); /*Highlight Closure = "Yes"*/
endcomp;
run;
%END;
%MEND;

%ByNcv;

ODS EXCEL CLOSE;

[PhilC]

I don't think SAS will ever do this well as Excel itself.  If you are not against using VBA macros you can try this:

"VBA to select all columns in a worksheet and auto adjust all columns width in Excel 2010"- https://...

[ph8252]

Hi PhiC,

Thanks for the suggestion but VBA macros may not be a viable option for my outputs. Would love to hear if there is any other way.

[PhilC]

I wrote this code to see how different, if different, the other spreadsheets would be, using SASHELP.CARS.  The answer was yes it allows different alignments.  It attempts to autofit column widths to the data.  But it doesn't do it well IMO. SAS doesn't "know" what your Excel's default font is so it, I believe, makes a conservative guess.   Another way to put it, SAS can't Autofit column widths like Excel, it must calculate the column widths, with an educated guess, before Excel is open to view the file.  The question becomes, to me, how do you get to SAS to fit column widths well...  as well as Excel?

ODS TAGSETS.EXCELXP did this job better.  It had the option WIDTH_FUDGE.  On my Excel, with the font I use, I always set WIDTH_FUDGE=0.7.  I was almost always, 99.99%, happy with its choice of automatically calculated column widths.  You might try TAGSETS.EXCELXP.

data cars;
  retain N (0);
  set sashelp.CARS;  
    by Make;
  if first.Make
    then N+1;
run;

%MACRO Rpt /parmbuff;
  ODS Excel 
    OPTIONS (Orientation = 'landscape' 
    sheet_name = "OB List"
    sheet_interval = 'Proc'
    embedded_titles='yes' 
    AUTOFILTER ="All"
    FLOW="TABLES"
    ROW_HEIGHTS="39, 18"); 
    ods listing close;
  proc print NoObs data=work.Cars(obs=30);
    where &syspbuff;
    var _all_;
  run;
%Mend;

%macro byMake;
  %do n=1 %to 3;
    ODS Excel 
      OPTIONS (Orientation = 'landscape' 
      sheet_name = "OB List"
      sheet_interval = 'Proc'
      embedded_titles='yes' 
      AUTOFILTER ="All"
      FLOW="TABLES"
      ROW_HEIGHTS="39, 18"); 
    %Rpt(N=&n)
  %end;
%Mend;

ODS Excel file="test.xlsx";
  %Rpt(1);
  %byMake;
ODS EXCEL CLOSE;

[ph8252]

PhilC, Thank you for the suggestion. I never try TAGSETS.EXCELXP so I did a little research on that and it seems you need to use width_fudge along with width_points. I played around a little bit with your suggestion of width_fudge='0.7' and width_points='0.7' and it is making weird adjustment to the column widths with some shrink too much that none of the text is visible and some expand to as wide as your view and leave a lot of unwanted white space. I have tried changing the values in the two options and the change seems to only apply the width of the table. Am I missing something? Please advise.
If you don't mind, could you share a sample code that can somehow tailor my needs using TAGSETS.EXCELXP?

[PhilC]

I qualified my use of the value 0.7 as the value that worked for me.  If you search this board, you will find other people using different values of WIDTH_FUDGE.  You may need to make WIDTH_FUDGE 0.75, or higher. 

I have never used WIDTH_POINTS.  Googling it I saw a mention of a problem note number, check for SAS notes incase you are encountering problems described in any SAS Notes.