topic Re: Find sum of multiple positive and negative values in SAS Programming

Find sum of multiple positive and negative values

JoshuaHarris — Wed, 26 Jan 2022 19:08:37 GMT

I have an "amount" column in a dataset which has several positive and negative values. I want to create one output which has same positive and negative numbers(eg: - 56,56,-72,72) and another output(excludes the data in first output) which has total values of any combination of positive and negative numbers equals to 0. (eg:, -5,-10,15), (-90,10,-5,85).. Is it possible to do this in SAS.. Please suggest..

Re: Find sum of multiple positive and negative values

PeterClemmensen — Wed, 26 Jan 2022 19:26:29 GMT

Can you provide some sample data and an explanation from that?

Re: Find sum of multiple positive and negative values

JoshuaHarris — Thu, 27 Jan 2022 13:34:11 GMT

Sure. Please find below the sample data
Amount_column
-56
76
10
-76
56
20
-45
-3
15
-35
40
4
4
First output should hv
Amount_col
-56
56
76
-76

Second output should have
Flag amount_col
A 10
A 20
A 15
A - 45
B 40
B 4
B 4
B - 45
B - 3
if you sum all A, then it will b 0 and if you sum all B, it will be 0..(offset)
Or even one output will be better
Flag amount_col
Zero 56
Zero -56
Zero - 76
Zero 76
A 10
A 20
A 15
A - 45
B 40
B 4
B 4
B - 45
B - 3

Re: Find sum of multiple positive and negative values

PeterClemmensen — Wed, 26 Jan 2022 20:13:31 GMT

Ok. The first output is quite simple.

Regaring the second output.. Do you want ALL combinations of values that equal zero when summed?

Re: Find sum of multiple positive and negative values

JoshuaHarris — Wed, 26 Jan 2022 20:18:20 GMT

Yes all combinations of values(positive and negative) that equal to 0 but once we identified a set of numbers thats sum is 0,that number/row item shouldn't be used again

Re: Find sum of multiple positive and negative values

Ksharp — Thu, 27 Jan 2022 13:18:24 GMT

I think you can't get job done simply by data step code.

You need SAS/OR to solve this kind of question.

Post it at OR forum

https://communities.sas.com/t5/Mathematical-Optimization/bd-p/operations_research

And calling out @RobPratt

Re: Find sum of multiple positive and negative values

RobPratt — Fri, 28 Jan 2022 15:45:45 GMT

The following code uses PROC OPTMODEL in SAS/OR or SAS Optimization to maximize the number of zero-sum groups:

data have;
   input amount;
   datalines;
-56
76
10
-76
56
20
-45
-3
15
-35
40
4
4
;

proc optmodel;
   /* declare parameters and read input data set */
   set OBS;
   num amount {OBS};
   read data have into OBS=[_N_] amount;
   num numGroups = card(OBS)/2;
   set GROUPS = 1..numGroups;
   
   /* declare decision variables */
   var UseGroup {GROUPS} binary;
   var Assign {OBS, GROUPS} binary;

   /* declare objective */
   max NumGroupsUsed = sum {g in GROUPS} UseGroup[g];

   /* declare constraints */
   con OneGroupPerObs {i in OBS}:
      sum {g in GROUPS} Assign[i,g] <= 1;
   con GroupSumZero {g in GROUPS}:
      sum {i in OBS} amount[i] * Assign[i,g] = 0;
   con AssignImpliesUse {i in OBS, g in GROUPS}:
      Assign[i,g] <= UseGroup[g];
   con UseImpliesAssign {g in GROUPS}:
      2*UseGroup[g] <= sum {i in OBS} Assign[i,g];
   con Symmetry {g in GROUPS diff {1}}:
      UseGroup[g] <= UseGroup[g-1];

   /* call MILP solver */
   solve;

   /* create output data set */
   num assignedGroup {OBS};
   for {i in OBS} do;
      for {g in GROUPS: Assign[i,g].sol > 0.5} do;
         assignedGroup[i] = g;
         leave;
      end;
   end;
   create data want from [i] amount assignedGroup;
quit;

The resulting optimal solution has 4 groups:

Obs	i	amount	assignedGroup
1	1	-56	4
2	2	76	2
3	3	10	.
4	4	-76	2
5	5	56	4
6	6	20	3
7	7	-45	1
8	8	-3	1
9	9	15	3
10	10	-35	3
11	11	40	1
12	12	4	1
13	13	4	1

See also Solved: Net Groups of Lines in a Table to $0.00 - SAS Support Communities for an alternative greedy approach.

Re: Find sum of multiple positive and negative values

PeterClemmensen — Fri, 28 Jan 2022 07:14:28 GMT

@RobPratt, if I could push the 'like' button more than once, I would.

Re: Find sum of multiple positive and negative values

JoshuaHarris — Thu, 03 Feb 2022 10:33:36 GMT

Thanks a lot for your speedy response and help

Obs	i	amount	assignedGroup
1	1	-56	4
2	2	76	2
3	3	10	.
4	4	-76	2
5	5	56	4
6	6	20	3
7	7	-45	1
8	8	-3	1
9	9	15	3
10	10	-35	3
11	11	40	1
12	12	4	1
13	13	4	1

Obs	i	amount	assignedGroup
1	1	-56	4
2	2	76	2
3	3	10	.
4	4	-76	2
5	5	56	4
6	6	20	3
7	7	-45	1
8	8	-3	1
9	9	15	3
10	10	-35	3
11	11	40	1
12	12	4	1
13	13	4	1

Obs	i	amount	assignedGroup
1	1	-56	4
2	2	76	2
3	3	10	.
4	4	-76	2
5	5	56	4
6	6	20	3
7	7	-45	1
8	8	-3	1
9	9	15	3
10	10	-35	3
11	11	40	1
12	12	4	1
13	13	4	1