The SAS Output Delivery System and reporting techniques

RE: Proc Report

Reply
Regular Contributor
Posts: 221

RE: Proc Report

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.

Grand Advisor
Posts: 9,576

Re: RE: Proc Report

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;

Regular Contributor
Posts: 221

Re: RE: Proc Report

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.

Grand Advisor
Posts: 9,576

Re: RE: Proc Report

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,210

Re: RE: Proc Report

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;

Regular Contributor
Posts: 221

Re: RE: Proc Report

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;

Esteemed Advisor
Esteemed Advisor
Posts: 7,210

Re: RE: Proc Report

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

Regular Contributor
Posts: 221

Re: RE: Proc Report

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.

Ask a Question
Discussion stats
  • 7 replies
  • 664 views
  • 0 likes
  • 3 in conversation