topic Re: Macro to split dataset into 4 tables based on input table size in SAS Programming

Macro to split dataset into 4 tables based on input table size

PegaZeus — Tue, 07 Apr 2020 16:03:03 GMT

I'm interested in developing a macro that can split a table into 4 datasets proportional to the size of the input table. Four tables with an equal (or roughly equal) number of records. So to create a macro that takes a table and breaks it up into 4 based on the size of the input table. So for a table with 100,000 records, I'd want it to break it into 4 tables with 25,000 records each.

Re: Macro to split dataset into 4 tables based on input table size

Kurt_Bremser — Tue, 07 Apr 2020 16:10:18 GMT

data
  want1
  want2
  want3
  want4
;
set have;
select (mod(_n_,4));
  when (0) output want1;
  when (1) output want2;
  when (2) output want3;
  when (3) output want4;
end;
run;

I guess it can be seen how this step can be made dynamic.

Re: Macro to split dataset into 4 tables based on input table size

FreelanceReinh — Tue, 07 Apr 2020 16:53:22 GMT

Hi @PegaZeus,

Just to add a variant of Kurt Bremser's solution: With two modifications to his code you can achieve a partition into four blocks of consecutive observations of dataset HAVE (first, second, third and fourth quarter):

set have nobs=n;
select (int(4*(_n_-1)/n));

Edit: If you use this in a macro, you should name variable n differently, e.g. ___n, or dynamically in order to avoid name conflicts with a possibly existing variable n in dataset HAVE.