How to use a lookup table using iterations?

[BarryP]

But let's say your customer has 2A's and 2B's,  And let's also say you have lookup codes   1-A1, 1-A2B1,  1-B1, and 1-A1B2,  but not 1-A2B2.  Then you have two possible solutions (1-A2B1+1-B1  or   1-A1B2+1-A1).  What criteria would you want to use to chose between them?

That's the part I think I'm going to have to hard code.  Right now here's the criteria:

If Inst_A gt 6 and Inst_B = 0............

Else If Inst_A gt1 and Inst_B gt 1............

Else If Inst_A = 1 and Inst_B gt 1...........

Else If Inst_A gt 1 and Inst_B = 1.............


So in this case it would calculate it as the second solution you have.



So question:  How would I lookup these values?  I'm seeing how I can put the lookup table in an array, and then look up the i'th element of the array.  Is there a way to have the "1-A2B1" element?

In Excel we can do this with either a Vlookup or a match.  I basically want to do the same idea in SAS.  Find the W, X, Y, Z values for 1-A1B2, add them to those fields, subtract the values from A & B fields, and then repeat the process, this time for 1-A1.

[Tom]

Similar to the knapsack problem.  So your knapsack can hold 2 A and 2 B.   You have a set of items that are each different combinations of A and B. Which combination of those can I fit into my knapsack.

Do you have SAS/OR licensed?  You could probably translate your problem into a resource allocation problem and solve it with one the tools in that suite.

[mkeintz]

Why does that have to be hard-coded.  Are there no criteria to use in choosing between the two (say whichever new combination has the lowest cost)?  Or if you want, you could choose whichever combination code is alphabetically first.  I would avoid hard-coding if at all possible.

[BarryP]

You make a great point.  I'll do my best to avoid it.  


Back to the array question.  I've got this piece:

proc transpose data=Old_Inst_Table out=OIT_Transpose;
     id Code;
run;


data Old_Inst_Array;

     set OIT_Transpose;
     array OIT_Array(8) A1 A1B1 A2 A2B2 A3 A4 A5 A6;

     x = OIT_Array(4);
     y = OIT_Array(5);

run;

So this would let y = the 4th element and y = the 5th element (I'm just doing this to test my array statement).  You said there's a way I could reference A2B2 and A3 directly without knowing that they are in the 4th and 5th positions?

[mkeintz]

Now that I understand that the problem is to start out dealing with collections of instruments to be replaced (where you simultaneously replace some number of A's and B's with new W's and X's), the array statement used in @PaigeMiller and my examples would not be useful until you've exhausted all the pre-coded combination lookups.  Only after you have no remaining qualifying multi-instrument lookups, would using the array statement with one instrument-type at a time be helpful.  That is the context in which I referred to having non-sequential discount levels  (i.e. prices for 1, 2, 5, and 10).

I now think your initial (and more difficult) problem is to look at all the multi-instrument codes that might qualify for a customer, and choose the best one.  Then reduce the count of old instruments (and increase the count of new instruments) according, and re-evaluate the remaining count of old instruments, again appealing first to the multi-instrument codes.

[BarryP]

Thanks.

Again, I'm assuming I'd need to do this using arrays?  Is that right?

Or is there some other way to lookup a cell value in a table?  As I said, I've done them as merges before, but I don't think I can iterate the merges.  ie....I can't merge one a value from one part of a lookup table and add it to another value.  I could do the merges over and over again with N number of tables and pick some value that would be more than the number of instruments an account would ever have, but that seems horribly inefficient.

It seems almost like an array would be a way to, more or less, pull a field out of a table and then use it to look up values that I would then add to my totals as I go through the iterations.

[BarryP]

Well sadly I think this programming exercise might be beyond my ability level.  Here's the solution I am settling on.  It's hard coded, but the good news is that the section of code that may need to be changed can be programmed pretty easily in the excel table, so if the change is made in excel, I can just copy and pate the code here.

If there's any tips or tricks from here, I'm all ears.  I apologize to those who's advice I seem to have ignored.  There's a whole lot of syntax that I've poured through that I've never seen before, and I've literally spent a day and half trying to figure out a better way to do it.  It seems like its not a simple solution.  

Data Cust_Table;

     Input Customer $ Inst_A Inst_B;

datalines;
CustA 1 0
CustB 2 1
CustC 6 0
CustD 7 0
CustE 7 1
CustF 5 2
;

run;

Data Old_Inst_Table /* For reference only */;

     input Code $ Inst_A Inst_B Inst_Y Inst_Z;

datalines;
A1   1 0 1 1
A1B1 1 1 2 2
A2   2 0 3 3
A2B2 2 1 4 4
A3   3 0 5 1
A4   4 0 6 2
A5   5 0 7 3
A6   6 0 8 4
;
run;

Data Results_1;

     set Cust_Table;

     Inst_Y = 0; Inst_Z = 0; A_rem = Inst_A; B_rem = Inst_B;

     Do Until (Loop_Control = 0);

          if A_rem = 1 and B_rem = 0 then do; A_rem = A_rem - 1; B_rem = B_rem - 0; Inst_Y = Inst_Y + 1; Inst_Z = Inst_Z + 1; end;
          if A_rem = 1 and B_rem = 1 then do; A_rem = A_rem - 1; B_rem = B_rem - 1; Inst_Y = Inst_Y + 2; Inst_Z = Inst_Z + 2; end;
          if A_rem = 2 and B_rem = 0 then do; A_rem = A_rem - 2; B_rem = B_rem - 0; Inst_Y = Inst_Y + 3; Inst_Z = Inst_Z + 3; end;
          if A_rem = 2 and B_rem = 1 then do; A_rem = A_rem - 2; B_rem = B_rem - 1; Inst_Y = Inst_Y + 4; Inst_Z = Inst_Z + 4; end;
          if A_rem = 3 and B_rem = 0 then do; A_rem = A_rem - 3; B_rem = B_rem - 0; Inst_Y = Inst_Y + 5; Inst_Z = Inst_Z + 1; end;
          if A_rem = 4 and B_rem = 0 then do; A_rem = A_rem - 4; B_rem = B_rem - 0; Inst_Y = Inst_Y + 6; Inst_Z = Inst_Z + 2; end;
          if A_rem = 5 and B_rem = 0 then do; A_rem = A_rem - 5; B_rem = B_rem - 0; Inst_Y = Inst_Y + 7; Inst_Z = Inst_Z + 3; end;
          if A_rem = 6 and B_rem = 0 then do; A_rem = A_rem - 6; B_rem = B_rem - 0; Inst_Y = Inst_Y + 8; Inst_Z = Inst_Z + 4; end;

          else if A_rem gt 6 and B_rem = 0 then do; A_rem = A_rem - 6; B_rem = B_rem - 0; Inst_Y = Inst_Y + 8; Inst_Z = Inst_Z + 4; end;
          else if A_rem gt 1 and B_rem gt 1 then do; A_rem = A_rem - 1; B_rem = B_rem - 1; Inst_Y = Inst_Y + 2; Inst_Z = Inst_Z + 2; end;
          else if A_rem = 1 and B_rem gt 1 then do; A_rem = A_rem - 1; B_rem = B_rem - 1; Inst_Y = Inst_Y + 2; Inst_Z = Inst_Z + 2; end;
          else if A_rem gt 2 and B_rem = 1 then do; A_rem = A_rem - 2; B_rem = B_rem - 1; Inst_Y = Inst_Y + 4; Inst_Z = Inst_Z + 4; end;

          Loop_Control = A_rem + B_rem;

     end;

run;