topic Re: Split table into multiple tables with specific count in SAS Programming

Split table into multiple tables with specific count

JT99 — Wed, 14 Feb 2024 10:48:28 GMT

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.

Re: Split table into multiple tables with specific count

PaigeMiller — Wed, 14 Feb 2024 11:55:00 GMT

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;

Re: Split table into multiple tables with specific count

Kurt_Bremser — Wed, 14 Feb 2024 12:35:04 GMT

%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.

Re: Split table into multiple tables with specific count

mkeintz — Wed, 14 Feb 2024 21:14:15 GMT

@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.

Re: Split table into multiple tables with specific count

Kurt_Bremser — Wed, 14 Feb 2024 21:31:28 GMT

I think this will do it:

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

Re: Split table into multiple tables with specific count

PaigeMiller — Wed, 14 Feb 2024 23:05:37 GMT

@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.

Re: Split table into multiple tables with specific count

JT99 — Wed, 14 Feb 2024 23:50:48 GMT

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

Re: Split table into multiple tables with specific count

Reeza — Wed, 14 Feb 2024 23:51:04 GMT

Wrote a macro for this a while back:

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

Re: Split table into multiple tables with specific count

JT99 — Thu, 15 Feb 2024 00:06:16 GMT

Perfect! Thank you!