topic Re: ODS EXCELXP tagset and column widths in ODS and Base Reporting

ODS EXCELXP tagset and column widths

DefconBond007 — Wed, 25 Jan 2012 14:20:25 GMT

Hi,

I am facing a problem while applying column widths using ODS with EXCELXP tagset.

1. There are many cloumns(around 40) and I want to increase the cell width for 10, 15 and 30th column only, so that those values fit into the columns.( using autofit_height & absolute_column_width )

I am applying like this: absolute_column_width = ' 4,5,5,4,4,4,4,4,4,30,....40,.............30 '; so like this it is not so efficient so ..

the code is :

ODS tagsets.ExcelXP PATH='/abcg' FILE="abc.xls" STYLE=xlsansprinter;

OPTIONS(autofit_height = 'yes'

absolute_column_width = '4,5,5,4,4,4,4,4,4,30,....40,.............30 '

sheet_name = "YTD &g_Year");

PROC report DATA=abc NOWD;
COLUMN col1 col2 col3 ...........col30 ;

DEFINE col1 / order noprint;
DEFINE cat2 / order noprint;

DEFINE col10 / order noprint;

DEFINE col15 / order noprint;

DEFINE col30 / order noprint;

run;

ODS _all_ CLOSE;

Thanks &Regards

Def

Re: ODS EXCELXP tagset and column widths

Cynthia_sas — Thu, 26 Jan 2012 00:45:59 GMT

Hi:

I must admit that I am a bit confused by your posted code. I would be surprised if you got anything out of Excel. The concept of NOPRINT with PROC REPORT is that NOPRINT is an option that allows you to USE a column in your REPORT code, but essentially HIDE the column from the output. So, there is no point in setting a column width for a NOPRINT column. As far as I can see, it looks like ALL your columns are NOPRINT columns????

What are you actually seeing in Excel? I'm curious? Are you getting any results? What do you mean when you say: " I want to increase the cell width for 10, 15 and 30th column only, so that those values fit into the columns.( using autofit_height & absolute_column_width )" -- in your posted code, all those column numbers are NOPRINT.

Can you explain a bit more about your data and/or post an example (screenshot) of what you're seeing in Excel and/or what you want to see in Excel.

And as just a test, I use NOPRINT with the PROC REPORT in the program below...using NOPRINT on the first 4 columns of the report and only showing name. If you run the code and look at the output, you will see that without NOPRINT (title 1), NAME appears in column E in the worksheet; however with the use of NOPRINT (title 2), NAME appears in column A in the worksheet. This is why I am not sure whether the partial code you posted will get your desired results. Messing with absolute_column_width won't do any good if the columns are not there.

cynthia

ods tagsets.excelxp file='c:\temp\lots_of_noprint.xml'

style=sansprinter options(embedded_titles='yes');

proc report data=sashelp.class nowd;

title '1) Without NOPRINT, NAME in COL E';

column age sex height weight name;

define age / display ;

define sex / display ;

define height / display ;

define weight / display ;

define name / display;

run;

proc report data=sashelp.class nowd;

title '2) With NOPRINT, NAME in COL A';

column age sex height weight name;

define age / display noprint;

define sex / display noprint;

define height / display noprint;

define weight / display noprint;

define name / display;

run;

ods tagsets.excelxp close;

Re: ODS EXCELXP tagset and column widths

DefconBond007 — Fri, 27 Jan 2012 12:10:56 GMT

Hi Cinthiya...sorry for mis typing the code..( specifying the option NOPRINT )

My question is: I am having around 40+ columns and they are randomly generated through code using across option in proc report.(similar to month& year like Jan2005,feb2005,mar2005,...,Dec 2010 as columns coming from a across option.)

So some columns require more widths, and I used absolute_column_width = ' 4,5,5,40,4,4,4,4,4,30,....40,.............30'.

But the total number of columns will change according to the range of years. so how can we specify in excelxp tagset/ ods to adjust the column widths according to their lengths of values.

I used the option style(column)={cellwidth=3in} style(header)={cellwidth=3in} for known number of columns. But for randomly generating columns ????

Re: ODS EXCELXP tagset and column widths

Cynthia_sas — Fri, 27 Jan 2012 18:15:50 GMT

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;