ODS Excel column widths not correct

[Doug____]

In a proc report step I have specific column widths declared - but they do not seem to make any difference for any of the columns. The example below shows three columns. Two of them appear as they should one does not - regardless of cellwidth, width, or any other setting. I prefer not to use absolute column width if I can avoid it.  The define statements are shown first then a screenshot of the output.

define volume/"Volume (000s)" format = comma32. style(column)={cellwidth = 4.0in} style = { htmlstyle="mso-number-format:'#,###'"};

[ballardw]

Show the entire proc report code and the ODS excel statement and options, or which ever destination you are using.

And remember that if you send output to Excel and have more than one table on the tab that a previous table may have set the column width. And Excel doesn't allow cell width to vary by row. So the entire column has the same width.

[Doug____]

proc report data = rank_ratio23 missing ;
where rank_ratio ne . and upcase(major_market) = upcase("%bquote(&&mm&i)");
columns MULTIPLE_CUTOFF major_market cat tenure2 title_group fa_code financial_advisor tenure_pos cutoff total_kpi_revenue salary bonus adjuster adjuster = adjusterx total_kpi_revenue = si2 salary = sal2 bonus = bon2
ratio ratiox variance deposit_bal deposit_volume net_deposit loan_bal loan_volume net_loan
;
define major_market / 'Major Market' order = data group noprint style(column)=[just = left width=1000% tagattr='wrap:No'] format=$F_MAJMKT. preloadfmt;
define cat / "Category" order = data group style(column)=[just = left width=1000% tagattr='wrap:No'];
define title_group /'Title^nGroup';
define fa_code /'FA';
define financial_advisor/'Financial Advisor' style(column)=[just = left width=1000% tagattr='wrap:No'];
define tenure2 /'Tenure^nCategory^n(Years)' group descending order = internal style(column)=[just = left width=1000% tagattr='wrap:No'] f = tenureb.;
define tenure_pos/'Tenure^n(Years)' mean f = comma10.1 ;
define ratio/'Ratio' display f=comma10.1 style = [fontweight = bold] noprint;
define cutoff/'Cutoff^nRating' order = data display style(column)=[fontweight = bold just = left width=1000% tagattr='wrap:No'] f=cutoff.;
define total_kpi_revenue/' spread' noprint ;
define salary / 'salary' noprint ;
define bonus /'bonus' noprint ;
define adjuster/ 'adjuster' noprint ;
define ratiox/computed f=comma10.1 style = [fontweight = bold] 'Ratio';
define adjusterx/ mean analysis noprint ;
define sal2/analysis sum noprint;
define bon2/analysis sum noprint;
define si2/analysis sum noprint;
define net_loan/"Loan^nTransfers (000s)" format = comma32. style = { htmlstyle="mso-number-format:'#,###'"};
define net_deposit/"Deposit^nTransfers (000s)" format = comma32. style = { htmlstyle="mso-number-format:'#,###'"};
define loan_volume/"Loan^nVolume (000s)" format = comma32. style(column)={cellwidth = 4.0in} style = { htmlstyle="mso-number-format:'#,###'"};
define deposit_volume/"Deposit^nVolume (000s)" format = comma32. style = { htmlstyle="mso-number-format:'#,###'"};
define loan_bal /"Loan^nBalance (000s)" format = comma32. style = { htmlstyle="mso-number-format:'#,###'"};
define deposit_bal/"Deposit^nBalance (000s)" format = comma32. style = { htmlstyle="mso-number-format:'#,###'"};
define multiple_cutoff / noprint;
define variance / computed 'Variance' format = comma32.1 style = [fontweight = bold];
compute ratiox;

if cutoff = 0 then call define ('ratiox',"style","style=[color = green]");
else call define ('ratiox',"style","style=[color = red]");
endcomp;

compute cutoff;
if cutoff= 0 then do;
call define (_col_,"style","style=[color = green]");
end;
else do;
call define (_col_,"style","style=[color = red]");
end;
endcomp;

compute variance;
variance = ratiox - MULTIPLE_CUTOFF.sum;
if round(variance,0.1)> = 0 then call define ('variance',"style","style=[color = green]");
else call define ('variance',"style","style=[color = red]");
endcomp;

break after tenure2/skip;
break after major_market/skip;
break after cat/skip;
rbreak after/skip;

compute after major_market ;
cat = "Major Market Total";
call define('ratiox',"style","style=[fontstyle = italic color = black fontweight=light]");

endcomp;
compute after cat ;
cat = "Category Total";
call define('ratiox',"style","style=[fontstyle = italic color = black fontweight = light]");

endcomp;

compute after ;
cat = "Overall";
call define('ratiox',"style","style=[fontstyle = italic color = black fontweight = light]");
endcomp;
compute after tenure2 ;
line ' ';
endcomp;
run;

[ballardw]

Interestingly enough that big block of code does not contain the partial line of code in your initial question.

So, how does the code posted relate to the question? The only width= setting I see in it are 1000%, which may have issues with multiple columns having that width in one proc.

[Cynthia_sas]

Hi:
Without sample data, no one can run your code. And you did not show all of your ODS code, including options, so no one knows what options you had in effect when you ran your code. Since you only posted a fraction of output, no one knows what the full set of column headers looks like.

As a best practice, I avoid percents for width values. I have much more success with units of measure, either PX, IN or CM. I usually use IN as the WIDTH unit. I'm not even sure that 1000% for as many columns as you show would have any impact on the output. And I see that in some places you do specify IN and in other places you specify % -- I find Excel, RTF and PDF destinations to be unreliable with mixed units of measure.

Also, HTMLSTYLE is how you set Microsoft formats for an HTML-based destination (like MSOFFICE2K -- but if you're using TAGSETS.EXCELXP or ODS EXCEL, then you should be using TAGATTR). Just another reason why seeing your ODS destination statements would be useful.

Too bad you didn't provide even 5 lines of data and the rest of your code to test.

Cynthia
Other additional thoughts:
1) Are your numbers really large enough to support the comma32. format?

2) These statements serve no purpose --

break after tenure2/skip;
break after major_market/skip;
break after cat/skip;
rbreak after/skip;

SKIP is a LISTING only option and is ignored by ODS destinations like HTML, RTF, PDF and EXCEL

[Cynthia_sas]

Hi:

  Simplifying your code and using ODS EXCEL, I did not see any issues with the COLUMN headers in Excel:

  Here's the fake data from SASHELP.PRDSALE and the simplified code.

data rank_ratio23;
  set sashelp.prdsale;
  major_market=catx(' ',country,region);
  cat=catx(' ',region,division);
  tenure2=catx('-',year,quarter);
  title_group=catx(' ',division,prodtype);
  fa_code=prodtype;
  financial_advisor = product;
  tenure_pos = int(quarter*13.73);
  cutoff=day(month);
  deposit_bal = actual*113;
  net_deposit=actual*1.133;
  deposit_volume = predict*117;
  net_deposit= year*actual;
  loan_bal=year*predict;
  loan_volume=year*predict*3.173;
  net_loan=quarter*actual*5.713;
  if _n_ le 12 then output;
keep major_market cat 
     tenure2 title_group fa_code financial_advisor 
     tenure_pos cutoff
     deposit_bal deposit_volume 
     net_deposit loan_bal loan_volume net_loan;
run;
 
ods escapechar='^';
ods excel file='c:\temp\testcolumnwidth.xlsx'; 
proc report data = rank_ratio23 missing 
    style(header)={background=white color=black}
    style(column)={background=white color=black};
columns major_market cat 
        tenure2 title_group fa_code financial_advisor 
        tenure_pos cutoff
        deposit_bal deposit_volume 
        net_deposit loan_bal loan_volume net_loan;
define major_market / group 'Major Market' order=data noprint 
       style(column)=[just=left ] ;
define cat / "Category" order=data group 
       style(column)=[just=left width=1.25in];
define tenure2 /'Tenure^nCategory^n(Years)' group  
       style(column)=[just=left  ]  ;
define title_group /'Title^nGroup'
       style(column)=[just=left width=2in];
define fa_code / display'FA';
define financial_advisor/'Financial Advisor' 
       style(column)=[just=left ];
define tenure_pos/'Tenure^n(Years)' mean f=comma10.1 ;
define cutoff/'Cutoff^nRating' display 
       style(column)=[fontweight=bold just=left ];
define deposit_bal/"Deposit^nBalance (000s)" f=comma15. ;
define deposit_volume/"Deposit^nVolume (000s)" f=comma15. ;
define net_deposit/"Deposit^nTransfers (000s)" f=comma15. ;
define loan_bal /"Loan^nBalance (000s)" f=comma15. ;
define loan_volume/"Loan^nVolume (000s)" f=comma15. ;
define net_loan/"Loan^nTransfers (000s)" f=comma15. ;
compute after tenure2 ;
  line ' ';
endcomp;
run;
ods excel close;

  I took out your COMPUTE blocks and your NOPRINT items because they were not impacting the headers.

Cynthia