10-16-2014 09:09 PM
Hi....I am using Proc Report procedure to create a Summary report in Excel. What I would like to do is to be able to restrict the number of rows, say 100 rows on any given worksheet and if the dataset has more than 100 rows, then automatically a new worksheet is created an so forth until all the data in the dataset are reported on a worksheet. But the worksheets cannot have more than 100 rows in them. Each new worksheet is to have a header row. Is this possible....thanks in adavance.
10-17-2014 08:59 AM
Hi Ksharp.....I want to separate or partion the dataset so that the first 100 rows are on one worksheet and the following 100 rows are on the second worsksheet and so on such that if there ws 240 rows in the dataset, then I should end up with 3 worksheets where the first one would have the first 100 rows of the dataset, the second worksheet would have the next 100 rows from the dataset and the third worksheet would have the remaining 40 rows form the dataset.
10-17-2014 09:21 AM
ods tagsets.excelxp file="s:\temp\rob\test.xls";
set sashelp.vtable (where=(libname="SASHELP" and memname="CARS"));
do I=0 to nobs by 100;
call execute('ods tagsets.excelxp options (sheet_name="Sheet'||strip(put(floor(i/100),best.))||'");
if '||strip(put(I,best.))||' <= _n_ < '||strip(put(I+100,best.))||' then output;
proc report data=x nowd;
ods tagsets.excelxp close;
10-17-2014 08:12 PM
Thanks to everyone who have made suggestions. I ended up creating another column (Group = ceil(_n_/100)) and used the Sheet_interval='bygroup' option to create a multi sheet excel file and it seem to work fine.....Thanks once gain to All.