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.
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;
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.
Ou, That is a tough one . Hope Cynthia@sas could give you some good idea .
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;
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;
Whats the error, I have just copied and pasted the above and it works fine.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.