topic Re: RE: Proc Report in ODS and Base Reporting

RE: Proc Report

twildone — Fri, 17 Oct 2014 01:09:52 GMT

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.

Re: RE: Proc Report

Ksharp — Fri, 17 Oct 2014 12:47:10 GMT

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;

Re: RE: Proc Report

twildone — Fri, 17 Oct 2014 12:59:55 GMT

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.

Re: RE: Proc Report

Ksharp — Fri, 17 Oct 2014 13:13:53 GMT

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

Re: RE: Proc Report

RW9 — Fri, 17 Oct 2014 13:21:22 GMT

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;

Re: RE: Proc Report

twildone — Fri, 17 Oct 2014 13:41:04 GMT

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;

Re: RE: Proc Report

RW9 — Fri, 17 Oct 2014 14:01:05 GMT

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

Re: RE: Proc Report

twildone — Sat, 18 Oct 2014 00:12:15 GMT

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.