Date:         Thu, 19 Jan 2006 05:25:41 -0500
Reply-To:     "SASsy :-)" <sas__l@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "SASsy :-)" <sas__l@HOTMAIL.COM>
Subject:      Re: explain complex function call in sas program

On Thu, 19 Jan 2006 03:52:51 -0500, Peter Crawford
<peter.crawford@BLUEYONDER.CO.UK> wrote:

>Hi
>
>An interesting sas program is referred to in recent sas usage note
>
>http://support.sas.com/techsup/unotes/SN/016/016587.html
>SN-016587
>How to export SAS datasets greater than 65,536 Rows to Excel
>
>
>As one does, I try to make sense of what the sas code is doing ;-)
>
>But despite the comment block
>**************************************************************************;
>*this routine takes the original dataset and splits it based on the number;
>*of rows specified at macro invocation.  the default is 65535            *;
>*which is the maximum number of rows excel can handle                    *;
>**************************************************************************;
>      this step has me stumped where it derives a value for "bygrp"
>
>data byvals;
> set &libref..&dsname;
> bygrp=int(((_n_/&nrows)+1)-.00000001);
> sheetname=cats("&sheetname",put(bygrp,8.));
>run;
>
>Is there someone out there who can come up with something more
>obvious ( perhaps using ceil, floor or mod ), or even just an
>explanation of what it is attempting by subtracting 1e-8 !
>
>Best,  would be a more logical explanation as well as
>a simpler derivation.
>
>
>Peter Crawford

Hi, the subtraction is obviously to make bygrp change
on the 65536 th observation rather than 65535, which should
be included in sheet1 (since 65535/65535 +1 = 2 ).
But why 1e-8 ? My guess is that it's somewhat arbitrarily
chosen, but between the fractional part of 65536/65535=1,000015..
and 1e-12, which is the limit of the int function.. just
guessing!  :o)
"Ceil" seems to do the same trick here:

data test;
 do x=1 to 1000000;
  output;
 end;
run;

data byvals;
 set test;
 bygrp=int(((_n_/65535)+1)-.00000001);
 bygrp2=ceil(_n_/65535);
run;

proc freq data=byvals;
 table bygrp bygrp2;
run;

S