topic Re: Build groups on two conditions in SAS Programming

Build groups on two conditions

Preguntarius — Mon, 01 Jul 2024 14:27:53 GMT

Hi everyone, I have a SAS table with the variable "target" which has only two possible values: 0 and 1.

I want to build groups, starting from the first line, so that each group has at least 30 values 1 of "target" and so that each group has at least 2000 rows.

So, if f.e. in the first 2000 rows I already have 30 values 1 of "target", the first 2000 rows would build the first group. If not, I have to take more rows in the first group. And so on.

Does anybody has an Idea how to do it?

Thanks in Advance

Denis

Re: Build groups on two conditions

Astounding — Mon, 01 Jul 2024 16:44:54 GMT

Something like this ought to work:

data want;
   group = _n_;
   n_targets = 0;
   group_size = 0;
   do until (group_size >= 2000 and n_targets >= 30);
      set have;
      group_size + 1;
      n_targets + target;
      output;
   end;
run;

It's untested, but you have the data to try it out. As you might suspect, the last group might be a little smaller and you will need to figure out what to do with that.

Re: Build groups on two conditions

Preguntarius — Mon, 01 Jul 2024 17:35:26 GMT

Nice, thanks!