RE: Proc Report

[twildone]

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.

[Ksharp]

Not sure exactly what you mean by that .

Can't you add a dataset option to print only 100 obs?

proc report data=have(obs=100) nowd;

[twildone]

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.

[Ksharp]

Ou, That is a tough one . Hope Cynthia@sas could give you some good idea .

[RW9]

Hi,

Well:

ods tagsets.excelxp file="s:\temp\rob\test.xls";

data _null_;

     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.))||'");

                        data x;

                          set sashelp.cars;

                          if '||strip(put(I,best.))||' <= _n_ < '||strip(put(I+100,best.))||' then output;

                        run;

                        proc report data=x nowd;

                        run;');

     end;

run;

ods tagsets.excelxp close;

[twildone]

Hi RW9....Thanks for your response. Is there someting missing from part of your code as I am getting an error from the Proc Report statement.

                       proc report data=x nowd; 

                        run;');

     end;

[RW9]

Whats the error, I have just copied and pasted the above and it works fine.

[twildone]

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.