topic spacing problem with line statement when sending output from proc report to ExcelXP in ODS and Base Reporting

spacing problem with line statement when sending output from proc report to ExcelXP

AUTigers — Thu, 19 Apr 2012 20:32:04 GMT

Hello,

the row height for the first line statement ( I have more than 1 line statement for my report) is extremely big. Is there an option we can use to adjust? i noticed the Row-height option, but really don't believe it is what i need.

Thanks!

filename tagset url 'http://support.sas.com/rnd/base/ods/odsmarkup/excltags.tpl';

%include tagset;

ods listing close;

ods tagsets.excelxp file='C:\sample.xls'

options(

ABSOLUTE_COLUMN_WIDTH="5,5.5,3.0,3,3" );

proc report data=sashelp.class nowd;

column sex name age height weight N ;

define sex / order;

define name /display;

define height/sum;

define age/mean;

define weight/sum;

compute before sex;

s=sex;

endcomp;

compute after sex;

line "for Gender:" s $4.;

line "Average Age:" age.mean;

line "total Weight:" weight.sum;

endcomp;

run;

ods tagsets.excelxp close;

Re: spacing problem with line statement when sending output from proc report to ExcelXP

Cynthia_sas — Fri, 20 Apr 2012 04:25:38 GMT

Hi:

That does seem odd...the height of the row with the first LINE statement. This note indicated some type of problem with multiple LINE statements in a COMPUTE block: http://support.sas.com/kb/34/828.html -- not exactly what you are reporting, but indicates some kind of issue could still exist with multiple LINE statements and TAGSETS.EXCELXP.

You might want to work with Tech Support on that that question. However, in order to write out multiple summary rows, an alternative to using a LINE statement in the first place is to use multiple BREAK statements, as shown on pages 7 and 8 of this paper:

http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

cynthia

Re: spacing problem with line statement when sending output from proc report to ExcelXP

AUTigers — Fri, 20 Apr 2012 04:35:25 GMT

Thank you for looking into my problem. i tried many other ways to make it removing the extra spaces. only autofit_height option works, but it also make the appearance of report looks very strange. everything on the report was squeezed to fit the smallest height as possible. i will look into the paper you mentioned.

thank you again!