## HASH Table to repeat Multiple Values Plus check Subtotal

Occasional Contributor
Posts: 14

# HASH Table to repeat Multiple Values Plus check Subtotal

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 ;
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) :
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) :
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

Super User
Posts: 8,261

## Re: HASH Table to repeat Multiple Values Plus check Subtotal

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

Occasional Contributor
Posts: 14

## Re: HASH Table to repeat Multiple Values Plus check Subtotal

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

Super User
Posts: 8,261

## Re: HASH Table to repeat Multiple Values Plus check Subtotal

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 ;
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
```
Discussion stats
• 3 replies
• 70 views
• 0 likes
• 2 in conversation