Hello,   I wanted to get your opinion on how some of you would handle the problem I have. I have grouped data that i need processed a particular way. For each group (name), i would like to always retain the key associated with the best match type for that group, as well as the corresponding match type.   The match types for each of the "keys" are ordered from 1 to 10, with 1 being the best match for that key, and 10 the worst. So basically I want to over-write any record that isn't the "best match" within each group for each key type.   Please let me know if you need any further explanation of what I'm tying to accomplish...Thanks in advance!   data have1; input name $ 3. order 2. key_a 4. match_type_a 2. key_b 4. match_type_b 2.; datalines ; aaa 1 111 1 . 10 aaa 2 . 10 222 3 bbb 1 333 1 . 10 bbb 2 . 10 444 3 ; run;   Want: name order key_a match_type_a key_b match_type_b aaa 1 111 1 222 3 aaa 2 111 1 222 3 bbb 1 333 1 444 3 bbb 2 333 1 444 3 ... View more

This is a repost since I didn't get the answer I was hoping for previously.   I am trying to merge 3 datasets together so that I only have unique records at the end.   I have provided the code and an example of how I would like the final table to look like in the end. I have highlighted the part I am having issues with. The first table is the "base" table, and what I want to do is to either merge the keys from the other 2 tables if there is a match, or add (or append) the ones that don't have a match.It is also important to keep the "left-most" name. So in the case below, I want to keep name 'ghi' since it is in the first table, and put the key for the 2nd table in the "want" table on that record since the names match, and I also want to have the key for the 3rd table since the keys matched between 'ghi' in the 2nd table and 'vwx' in the 3rd table. I also want to not keep the 'vwx' from the 3rd table since it found a match.   The additional layer I need is for the query to also not add 'pqr' to the final table since it matches 'have1' to 'have2' on 'key', and 'have2' to 'have3' on 'name', which is the part i can't get right.   If I didn't explain that well or if anyone has any additional questions, please feel free to reach out. Thank you and I appreciated the help in advance.   Thanks, Tom       data have1; input name1 $ 3. key1 4.; datalines ; abc 111 def 112 ghi 113 ; run; 　 data have2; input name2 $ 3. key2 4.; datalines ; jkl 114 ghi 115 pqr 111 ; run; 　 data have3; input name3 $ 3. key3 4.; datalines ; stu 117 vwx 115 pqr 118 ; run;       name key1 key2 key3 abc 111 111 118 def 112     ghi 113 115 115 jkl   114   stu     117 ... View more

Hi,   I had a problem that Rob Pratt helped me with a little while ago and I was trying to modify the problem slightly and haven't been able to figure out how...What I am trying to do is create 2 groups (ID) with equal amounts of volume (x1-x3) where you can think of x1-x3 as month1, month2, and month3 and the monthly sales volume within each variable. The 2 groups need to have close to the same volume for each month. This is the part that I have done already...   This is the part i'm stuck on...The ID's currently are part of 4 groups that i want to combine into 2. What I am trying to figure out how to do, is only change the group if it is in ID "1" or "3". The tricky part (at least what I believe is the tricky part) is I don't want to change the group if the ID is in "2" or "4", but I do want to use those ID's volume into the total for the new group.   So in the end there should be 2 groupID's (1 and 2) and groupID 1 should consist of all of the original ID 2's volume, plus the new volume that comes from ID's 1 and 3. GroupID 2 should consist of all original ID 4's volume, plus the new volume thta comes from ID's 1 and 3. Here was the original set-up: data have;    input id $ curr_grp x1-x3;    datalines; a 1 10 15 8 b 1 11 14 9 c 1 12 16 10 d 1 9 16 8 e 2 8 12 10 f 2 8 10 11 g 2 11 14 12 h 2 13 10 9 i 3 8 16 15 j 3 10 11 8 k 3 11 15 14 l 3 16 13 10 m 4 10 15 14 n 4 12 8 14 o 4 7 12 13 p 4 9 14 10 ; %let num_vars   = 3; %let num_groups = 2; proc optmodel;    set VARS   = 1..&num_vars;    set GROUPS = 1..&num_groups;    set  <STR> OBS;    num a {OBS, VARS}; /*   num weight {j in VARS} = 1 / max {i in OBS} a[i,j];*/ read data have  into OBS=[id] {j in VARS} <a[id,j]=col('x'||j)> ;    /* Assign[i,g] = 1 if observation i assigned to group g, 0 otherwise */    var Assign {OBS, GROUPS} binary;    con AssignOnce {i in OBS}:       sum {g in GROUPS} Assign[i,g] = 1;    con NearlyEqual {g in GROUPS}:       floor(card(OBS)/&num_groups) <= sum {i in OBS} Assign[i,g] <= ceil(card(OBS)/&num_groups);    impvar GroupSum {g in GROUPS, j in VARS} = sum {i in OBS} a[i,j] * Assign[i,g];    var MinSum {VARS}, MaxSum {VARS};    con MinSumCon {g in GROUPS, j in VARS}:       MinSum[j] <= GroupSum[g,j];    con MaxSumCon {g in GROUPS, j in VARS}:       MaxSum[j] >= GroupSum[g,j];    impvar Range {j in VARS} = MaxSum[j] - MinSum[j];    min Objective = sum {j in VARS} Range[j]; /*    min Objective = sum {j in VARS} weight[j] * Range[j];*/    solve;    print Assign;    print GroupSum;    num groupID {OBS};    for {i in OBS} do;       for {g in GROUPS: Assign[i,g].sol > 0.5} do;          groupID[i] = g;          leave;       end;    end;    create data want/*(drop=i)*/ from [i] {j in VARS} <col('x'||j)=a[i,j]> groupID; quit;     Thanks in advance for the help! ... View more