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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.