Date:         Thu, 7 Jul 2011 18:32:09 -0400
Reply-To:     Richard DeVenezia <rdevenezia@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Richard DeVenezia <rdevenezia@GMAIL.COM>
Subject:      Re: ExcelXP by group processing has unexpected blank line
Comments: To: Joe Matise <snoopy369@gmail.com>
In-Reply-To:  <CAM+YpE9ZDsF_iV=WdgN+QSBVAZG3y13XBvgmXZVMXc40t=AmKQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

Hi Joe:

Thanks for the suggestion.  Unfortunately the skip_space only removed
unwanted blank rows.  The repeated title (due to continued?) still
occurs.
I tried the trick in KB 40327 (http://support.sas.com/kb/40/327.html)
but that did not do anything for me in ExeclXP destination.

Richard

On Thu, Jul 7, 2011 at 6:16 PM, Joe Matise <snoopy369@gmail.com> wrote:
> Been a very long time since I dealt with this, but if I recall correctly
> there's a setting somewhere that controls the number of skip lines after
> certain events [title, page, byline, and something else perhaps].  It's set
> literally to "1,1,0,1" or something to that effect.  I think it is the
> SKIP_SPACE setting, but I'm not 100% sure.
> -Joe
>
> On Thu, Jul 7, 2011 at 5:04 PM, Richard Devenezia <rdevenezia@gmail.com>
> wrote:
>>
>> Hi:
>>
>> The data 'foo' contains several groups each with 225 rows.
>>
>> The Excel file created with a PRINT, BY and TITLE "#BY..." has a break
>> (blank cell) at every 100th line within a by group.  After the break the
>> title is repeated.
>>
>> The listing destination is similar, but has 98 rows before each split (I
>> guess the other 2 are used for the '(continued)' notice.)
>>
>> Any ideas why this happens how to prevent it ?
>>
>> ------
>>
>> OPTIONS NOCENTER NOBYLINE;
>>
>> data foo;
>>  do row = 1 to 1000;
>>    group = floor((row-1)/225);
>>    output;
>>  end;
>> run;
>>
>> ods listing;
>>
>> ods tagsets.excelxp file="\temp\sample-%sysfunc(monotonic()).xls.xml"
>> style=journal;
>> ods tagsets.excelxp options
>> ( sheet_name = 'One'
>>  sheet_interval = 'none'
>>  embedded_titles = 'yes'
>>  suppress_bylines = 'yes'
>> );
>>
>> title h=10pt bold "Details for the [#BYVAR1] = [#BYVAL1]";
>>
>> proc print noobs data=foo;
>>  by group;
>>  var row;
>> run;
>>
>> ods tagsets.excelxp close;
>> ------
>>
>> Thanks,
>> Richard DeVenezia