Hi: Your original code did not show any ACROSS items. Basically, all I can do is point you to the documentation for the ABSOLUTE_COLUMN_WIDTH sub-option (which you can see if you specify options(doc='Help') in your code: From the LOG info on ABSOLUTE_COLUMN_WIDTH suboption: Absolute_Column_Width: Default Value 'None' Values: None, Number, list of numbers. This option works similarly to the default column width option The difference is that these widths will be used regardless of any column widths the procedure might provide. The value should be the width in characters. If the value of this option is a comma separated list. Each number will be used for the column in the same position. If the table has more columns, the list will start over again. So, I think, without seeing your actual code, or understanding more about what you mean when you say "randomly generated", that it would be some kind of SAS Macro solution you have to pursue if you are intent on using ABSOLUTE_COLUMN_WIDTH to adjust your column sizes. However, using simple CELLWIDTH= or WIDTH= style overrides, I am able to make the INVENTORY column bigger under each REGION value, as shown in the attached screen shot. The code below was used to create the output. You might want to work with Tech Support on this, because your ACTUAL code and how your data is "randomly" generated can impact the solution. To me, a "random" number of columns is different than data where some years or ACROSS items have more months than others. You asked "how can we specify in excelxp tagset/ ods to adjust the column widths according to their lengths of values." -- and I think the answer is NOT to use ABSOLUTE_COLUMN_WIDTH. There are other sub-options, such as DEFAULT_COLUMN_WIDTH, WIDTH_FUDGE and WIDTH_POINTS. If you read about the description of these sub-options (in your SAS Log), it seems to me that these sub-options are more suited than using ABSOLUTE_COLUMN_WIDTH because they allow you to use the length set by SAS and then "fudge" the width by just a bit in order to make sure that it is wide enough when it gets to Excel. cynthia ** cellwidth method; ods tagsets.excelxp file='c:\temp\adjust_col.xml' style=sasweb options(doc='Help'); proc report data=sashelp.shoes nowd; where region in ('Asia', 'Canada'); column product region,(sales inventory returns); define product / group; define region /across 'Region Averages'; define sales / mean f=comma8. style(column)={cellwidth=.5in}; define inventory / mean f=comma8. style(column)={cellwidth=1.25in}; define returns / mean f=comma8. style(column)={cellwidth=.5in}; run; ods _all_ close; ** "fudge" width method; ods tagsets.excelxp file='c:\temp\adjust_col2.xml' style=sasweb options(doc='Help' default_column_width='3' width_fudge='.8'); proc report data=sashelp.shoes nowd; where region in ('Asia', 'Canada'); column product region,(sales inventory returns); define product / group; define region /across 'Region Averages'; define sales / mean f=comma8.; define inventory / mean f=comma8.; define returns / mean f=comma8.; run; ods _all_ close;
... View more
