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
What does HASH have to do with this problem? Your sample data looks to be simple 1-to-N merge.
Merge will only solve ist problem...second condition is to do a check on subtotal for that variable.
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.