topic Re: Compare SUM of any 2 or more values from one dataset with SUM of any 2 or more values from anoth in SAS Enterprise Guide

Compare SUM of any 2 or more values from one dataset with SUM of any 2 or more values from another

Dawn3 — Sun, 02 Jun 2019 21:34:58 GMT

Hello,
How to find if SUM of any 2 or more values from one dataset matches SUM of any 2 or more values from another dataset?
There are always at least two values (VAR1 and VAR2) in each dataset, and number of VARs are not necessarily the same.

Currently I am using this table to drive the process:

data TEST.COMBINATIONS;

input VAR1 VAR2 VAR3 VAR4 VAR5 MAX_VAR_NUM CNT;

datalines;

1 2 . . . 2 1

1 3 . . . 3 2

2 3 . . . 3 3

1 2 3 . . 3 4

1 4 . . . 4 5

2 4 . . . 4 6

3 4 . . . 4 7

2 3 4 . . 4 8

1 2 3 4 . 4 9

1 5 . . . 5 10

2 5 . . . 5 11

3 5 . . . 5 12

4 5 . . . 5 13

3 4 5 . . 5 14

2 3 4 5 . 5 15

2 4 5 . . 5 16

1 2 3 4 5 5 17

;

run;

For example, if there are only two values in 1st dataset (MAX_VAR_NUM=2) than only SUM(VAR1, VAR2) is to be used.
If there are 3 values in 2nd dataset (MAX_VAR_NUM=3) than all combinations up to CNT 4 are to be compared with SUM(VAR1, VAR2) from 1st dataset:

SUM(VAR1, VAR2),
SUM(VAR1, VAR3),
SUM(VAR2, VAR3),
SUM(VAR1, VAR2, VAR3).

Is there a better way that hardcoding? The solution above is limited by the number of hardcoded combinations in the COMBINATIONS table.
Since SUM(VAR1, VAR2) = SUM(VAR2, VAR1) function LEXPERM was not helpful for this problem.

Thanks a lot!

Re: Compare SUM of any 2 or more values from one dataset with SUM of any 2 or more values from anoth

Ksharp — Mon, 03 Jun 2019 13:30:50 GMT

Try function GRAYCODE() .

Can you post these two datasets ?

Re: Compare SUM of any 2 or more values from one dataset with SUM of any 2 or more values from anoth

Dawn3 — Tue, 04 Jun 2019 04:36:28 GMT

Hi Ksharp,

Yes, the GRAYCODE() could be useful for this problem.

Let’s simplify it to one value being matched with two or more:
data TEST.A_TWO;

input TRANSACTION_ID AMOUNT MULTI_CNT CNT;

datalines;

100 2.10 1 1

100 3.10 2 2

200 3.00 1 3

200 2.50 2 4

200 2.50 3 5

200 1.00 4 6

;

run;

%let val_1=5; /* value to be matched */

%let transaction_id=200; /* within this transaction_id */

proc sql;

select count(*) into: MAX_NUM_OF_TRANS

from TEST.A_TWO

where TRANSACTION_ID = &transaction_id;

quit;

Now we know that there are 4 transactions of transaction_id=200 and we can use MULTI_CNT to identify them. The task remains to produce combinations: 1,2; 2,3;. . .; 1,2,3,4 to finally get combination '2,3' that leads to CNT 4 and 5 that uniquely identify the matches:
%let range=2,3;
proc sql;

select SUM(AMOUNT) from TEST.A_TWO

where TRANSACTION_ID = &transaction_id

and MULTI_CNT IN (&range);

quit;

If there are multiple combinations that produce the same SUM, the first one is sufficient.
Thanks a lot for the ideas.