I used the price and quantity to simplify the problem. The real problem is a little more complicated. We are replacing combinations of lab instruments with different combinations of new lab instruments. So its more like: 1 Inst_A & 1 Inst_B = 2 inst_X & 1 inst_Y 2 inst_A & 1 Inst_B = 1 inst_Z 2 inst_A & 2 Inst_B = 3 inst_X & 1 inst_Y 1 inst_A & 1 inst_C = 1 inst_X & 2 inst_Z etc. I have a list of ~100 different combinations of 6 instruments and what they translate to (different combinations of 5 instruments). The lookup table accounts for about 80% of our customer base. Its the remaining 20% that I'm trying to write algorithms for. For example, I have a lookup of 6 Inst_B & 1 Inst_C but I have a customer with 13 Inst_B and 4_Inst_C. My current algorithm translates 6 Inst_B & 1 Inst_C, then 6 Inst_B & 1 Inst_C again, then 1 Inst_B & 1_Inst_C, and then finally 1 Inst_C. Those translate to (respectively): 1 Inst_W, 3 Inst_Y, 1 Inst_Z 1 Inst_W, 3 Inst_Y, 1 Inst_Z 1 Inst_V, 1 Inst_Z 0 instruments I add them together to = 1 Inst_B, 2 Inst_W, 6 Inst_Y, 3 Inst_Z. When using the lookup table its pretty easy. I have a code that says, for example, 6B1C and the table has columns for V, W, X, Y, Z and just pulls them in with the merge. I haven't had the chance to look at the two methods proposed yet, so I don't know if it makes a difference when pulling in 5 different columns from combinations of 6 different columns. FWIW, here's a snippet of the code I'm currently using. It works just fine, but I'm trying to learn more efficient methods. Keep in mind that I omitted a bunch of code that makes this work. The gist is that in the I run through the loop, adding the new instruments and subtracting the old ones until Loop_Control = 0. Do Until (Loop_Control = 0); If Inst_B = 1 and Inst_C = 0 and Profile = 'A&B Only' then do; Inst_V = Inst_V + 1; Inst_W = Inst_W + 0; Inst_X = Inst_X + 0; Inst_Y = Inst_Y + 0; Inst_Z = Inst_Z + 1; Inst_B = Inst_B - 1; Inst_C = Inst_C - 0; end; If Inst_B = 1 and Inst_C = 1 and Profile = 'A&B Only' then do; Inst_V = Inst_V + 1; Inst_W = Inst_W + 0; Inst_X = Inst_X + 0; Inst_Y = Inst_Y + 0; Inst_Z = Inst_Z + 1; Inst_B = Inst_B - 1; Inst_C = Inst_C - 1; end;
If Inst_B gt 1 and Inst_C = 1 and Profile = 'A&B Only' then do; subprofile = 'out of scope'; Inst_V = Inst_V + 1; Inst_W = Inst_W + 0; Inst_X = Inst_X + 0; Inst_Y = Inst_Y + 0; Inst_Z = Inst_Z + 1; Inst_B = Inst_B - 1; Inst_C = Inst_C - 1; end;
If Profile = 'A&B Only' then Loop_Control = Inst_A;
else Loop_Control = 0; end;
... View more
