topic Re: Grouping in SAS Programming

Grouping

Pratik612_ — Sat, 15 Jun 2019 14:17:48 GMT

Hi Everyone,

I have a variable and has numeric values stored in it.I want to create the groups of those values whose sum is equal irrespective of number of values in each group. For eg,

T is a variable that has all the values:

Creating two groups of 14 each, the groups formed would be (1,3,4,6) and (2,5,7).

Kindly provide a generalized solution or idea because the variable actually has millions of rows.

Thanks.

Re: Grouping

Astounding — Sat, 15 Jun 2019 14:58:27 GMT

First, shorten the problem. The number of combinations to test is 2 raised to the power of # of observations. With millions of observations, you may not live long enough for a computer to perform all the calculations.

Questions: is T always an integer? Could two separate observations hold identical values for T? If so, would they count as 1 combination or 2?

Re: Grouping

Pratik612_ — Sat, 15 Jun 2019 16:29:03 GMT

Thank you, for asking for clarification and making my post more precise.
Firstly, I want to group them into 10 groups and answering your qts T is not always an integer and can hold duplicate values, also they should be treated as different combinations.

Re: Grouping

Patrick — Sat, 15 Jun 2019 23:47:18 GMT

@Pratik612_

"I want to group them into 10 groups"

This kind-of contradicts your initial statement where you're looking for combinations with the same sum.

With millions of values the number of possible combinations to test will exceed the computational power of your environment.

It feels like you should get back to the drawing board and try to come up with an algorithm that gives you a desired result with a different/amended method which requires less computing.

You could also try and explain here in detail the underlying problem you want to solve, what you have and what you need. There might be other ways to approach things and someone here might have solved a similar problem before.

Re: Grouping

Ksharp — Sun, 16 Jun 2019 12:27:01 GMT

Yeah. It is impossible mission . There are too many solutions to suit your condition .

But if you have SAS/OR ,maybe give it a try (I also doubt due to big data).

Post is at OR forum and calling @RobPratt

Re: Grouping

Tom — Sun, 16 Jun 2019 12:52:37 GMT

How precisely do they need to match?

Sort by descending value.

Assign the next value to the group with smaller current total.