HASH Table to repeat Multiple Values Plus check Subtotal

[jpm2478]

Hi,

I have 2 datasets A & B which I need to merge using Hash. set B has multiple values

1.. I want Set A to repeat the values and show all values of setB?

2.  while merging the 2 sets...I have another condition of Multiple of 20. I need to check the subtotal of Key variable. If the subtotal of key variable is greater than 20 then the last value needs to be adjusted for subtotal to be = 20. For example, 1st value of adata variable is 1 so the Multiple value x20 (1*20) will be 20. when Ist value is merged with setB  

After Merging
1	11
1	12
Checking subtotal - Variable adata
1	11
1	12
	23
Result
1	11
1	9
	20

data a ;
input key adata ;
cards ;
1 1                           /* 1*20 =20 */
2 2                          /* 2*20 =40 */
3 3                          /* 3*20 =60 */
4 4                         /* 4*20 =80 */
5 5                       /* 5*20 =100 */
6 6                      /* 6*20 =120 */
7 7                     /* 7*20 =140 */
;
run ;
data b ;
input key bdata ;
cards ;
1 11
1 12
3 31
4 4
6 61
6 62
6 63
7 7
;
run ;

Result (without 10x Multiple) :
Key Bdata adata
1 11 1
1 12 1
3 31 3
4 4 4
6 61 6
6 62 6
6 63 6
7 7 7

Result (with 10x Multiple) :
Key Bdata adata
1 11 1
1 9 1    /* changed to 9 since 11+9 will be 20  */
3 31 3
4 4 4
6 61 6
6 62 6
6 63 6
7 7 7

[Tom]

What does HASH have to do with this problem?  Your sample data looks to be simple 1-to-N merge.

[jpm2478]

Merge will only solve ist problem...second condition is to do a check on subtotal for that variable.

[Tom]

Just merge and make a running total.

Is this what you mean?

data want ;
  merge A B ;
  by key;
  retain total ;
  if first.key then total=0;
  new_b = bdata;
  total+bdata ;
  if last.key then do;
    diff = total - adata * 20 ;
    new_b = bdata - diff ;
    total= adata * 20;
  end;
run;

proc print;
run;

Obs    key    adata    bdata    total    new_b    diff

  1     1       1        11       11        11       .
  2     1       1        12       20         9       3
  3     2       2         .       40         .     -40
  4     3       3        31       60        60     -29
  5     4       4         4       80        80     -76
  6     5       5         .      100         .    -100
  7     6       6        61       61        61       .
  8     6       6        62      123        62       .
  9     6       6        63      120        -3      66
 10     7       7         7      140       140    -133