Split table into multiple tables with specific count

[JT99]

Hello! How do I split one table into multiple tables and limit the number of observations to 25 for each table? Then I need to export all tables in excel.
Ex. I have a table with 99 obs and I need to split it to 4 tables with the first 3 tables having 25 obs and the last having 24. The original table can have different number of obs.

[PaigeMiller]

Splitting tables is rarely necessary. You can leave this as one big table, with an identifier variable to indicate which of the four parts, then perform analyses BY this identifier variable.

Example:

data cars;
    set sashelp.cars;
    if mod(_n_,25)=1 then identifier+1;
run;

proc means data=cars;
    by identifier;
run;

If, for example, you only want to work on the data when identifier=3 (and not the rest of the data), you can do this too

proc means data=cars;
    where identifier=3;
run;

[Kurt_Bremser · Accepted solution]

%let size = 25;

data want;
set have;
retain group 1;
if mod(_n_,&size.) = 0 then group + 1;
run;

You now have a group designator which can be used in multiple ways, e.g. to switch sheets when using ODS EXCEL and a reporting procedure.

[mkeintz]

---

@Kurt_Bremser wrote:

%let size = 25;

data want;
set have;
retain group 1;
if mod(_n_,&size.) = 0 then group + 1;
run;

You now have a group designator which can be used in multiple ways, e.g. to switch sheets when using ODS EXCEL and a reporting procedure.

---

I agree with your comment.  But the code needs a minor tweak.  The first group above would only have 24 observations.

[Kurt_Bremser]

I think this will do it:

retain group 0;
if mod(_n_ - 1,&size.) = 0 then group + 1;

[PaigeMiller]

---

@Kurt_Bremser wrote:

I think this will do it:

retain group 0;
if mod(_n_ - 1,&size.) = 0 then group + 1;

---

Or my code earlier will do it as well.

[JT99]

Thank you all so much! Your replies are a big help.

[Reeza]

Wrote a macro for this a while back:

https://gist.github.com/statgeek/49e54641ceaf58bc4fe5dc2062bc89cb

[JT99]

Perfect! Thank you!