topic can't get column widths and heights to come out in ODS and Base Reporting

can't get column widths and heights to come out

wkossack_nspirehealth_com — Tue, 25 Jun 2013 21:16:19 GMT

I am trying to output to excel...is there a way to get the column widths and heights to come out?

I'm using tagsets.excelxp and the options row_heights= and absolute_column_width=

Re: can't get column widths and heights to come out

ballardw — Tue, 25 Jun 2013 21:26:56 GMT

Sample data and attempted code would help diagnosis issues.

Re: can't get column widths and heights to come out

wkossack_nspirehealth_com — Tue, 25 Jun 2013 22:44:50 GMT

for some stupid reason I was not able to paste in my first window.

am I leaving something out?

ods tagsets.ExcelXP path="c:\temp\" file='Technician_Quality_Report.xml' style=sasweb;

ods tagsets.excelxp options(embedded_footnotes='yes' embedded_titles='yes'

sheet_label='Country Summary ' ROW_HEIGHTS='10,10,10,20,10'

orientation='landscape'

absolute_column_width='25,15'

);

title1 'quality report';

proc print data=test;

var a b c d;

label

a='var a'

b='var b';

run; quit;

Re: can't get column widths and heights to come out

Cynthia_sas — Tue, 25 Jun 2013 22:53:20 GMT

Hi, in addition to sample data and code, have you searched the forum for previous postings? I know that there have been previous posts, with code examples on this issue. I generally use cellwidth as my first approach and then absolute_column_width as my next approach. When I use cellwidth, I don't necessarily have to adjust row heights. See the attached screen shot. I created this output using PROC REPORT. Did not use absolute_cell_width or row_height and the small column adjusted automatically.

cynthia

data class;

length newname $80;

set sashelp.class;

newname = catx(' ',name,repeat(substr(name,1,2),7));

newname = propcase(newname);

run;

** ODS TAGSETS.EXCELXP is making an XML file;

** Only control for cellwidth and use;

** NEWNAME twice on the report, 1 time with 2.5 in width;

** and second time with .5in width;

ods tagsets.excelxp file='c:\temp\xp.xml' style=sasweb;

proc report data=class nowd

style(header)={background=pink foreground=black};

column name newname newname=nnsmall age height weight;

define newname /style(column)={cellwidth=2.5in};

define nnsmall/style(column)={cellwidth=.5in};

run;

ods _all_ close;

Re: can't get column widths and heights to come out

wkossack_nspirehealth_com — Wed, 26 Jun 2013 02:55:28 GMT

ah but your using proc report

I'm using proc print. I don't see any examples using proc print.

I'm trying not to change the report too much from what users are used to seeing. The proc print works fine the old server which is failing so I have to port everything to a new server

Re: can't get column widths and heights to come out

Cynthia_sas — Wed, 26 Jun 2013 03:30:46 GMT

Hi:

The syntax is a bit different for PROC PRINT, but the results are the same for me. See the attached screenshot. Altered code is below.

cynthia

data class;

length newname $80;

set sashelp.class;

newname = catx(' ',name,repeat(substr(name,1,2),7));

newname = propcase(newname);

newsmall = newname;

run;

** ODS TAGSETS.EXCELXP is making an XML file;

** Only control for cellwidth;

ods tagsets.excelxp file='c:\temp\xp.xml' style=sasweb

options(embedded_titles='yes');

proc report data=class nowd

style(header)={background=pink foreground=black};

title '1) PROC REPORT';

column name newname newname=nnsmall age height weight;

define newname /style(column)={cellwidth=2.5in};

define nnsmall/style(column)={cellwidth=.5in};

run;

proc print data=class noobs

style(header)={background=pink foreground=black};

title '2) PROC PRINT';

var name;

var newname /style(data)={cellwidth=2.5in};

var newsmall /style(data)={cellwidth=.5in};

var age height weight;

run;

ods _all_ close;

Re: can't get column widths and heights to come out

wkossack_nspirehealth_com — Wed, 26 Jun 2013 16:03:26 GMT

with of the column has not been the big problem it is the width of the rows

I've been trying ROW_HEIGHTS='30,30,30,20,20,20' but it does not seem to have an effect

Re: can't get column widths and heights to come out

Cynthia_sas — Wed, 26 Jun 2013 23:15:14 GMT

Hi, As my code and screenshots above show, the height of the rows is automatically adjusted when I set the width of the columns. However, I don't understand why you are changing every possible value for ROW_HEIGHTS. Are you using PARSKIP? does your code have BYLINES, why bother to change those heights if you don't have them in your code? For example, in the code below, I change the table headers, table rows, title and footnote rows. I am changing ALL the title rows and ALL the footnote rows to be the same. I am changing ALL the "body/data" rows to be the same.

If you are still having issues with Row Height, then I suggest you open a track with SAS Tech Support.

cynthia

title;

ods listing close;

For the ROW_HEIGHTS suboption, the default values are:

Table_head : 0

Table : 0

Byline : 0

Title : 0

Footer : 0

PageBreak : 0

Parskip : 0

row_heights='table_head,table,byline,title,footer, pagebreak,parskip'

ods _all_ close;

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

options(doc='help' embedded_titles='yes'

embedded_footnotes='yes'

row_heights='30,20,0,50,70,0,0')

style=sasweb;

proc report data=sashelp.class(obs=3) nowd;

title j=l 'Title1:';

title2 j=l 'Title2:';

title3 j=l 'Title3:';

footnote 'The Footnote';

column age sex name height weight;

define age / order style(header)={cellwidth=2in};

define name / style(header)={cellwidth=2in};

define weight / style(header)={cellwidth=2in};

run;

ods tagsets.excelxp close;