topic Re: How to use a lookup table using iterations? in SAS Programming

How to use a lookup table using iterations?

BarryP — Sun, 19 Jan 2020 22:43:16 GMT

I've been struggling with this for a while and thought I'd throw the question up here to see what happens.

Consider this hypothetical. I've got a company that sells widgets.

1 for $5
2 for $9
3 for $13
4 for $16

Customer A buys 1, Customer B buys 2, C buys 3, D buys 4. This can be solved with a simple merge.

What if I have a customer who buys 9? He would basically buy 4, 4, and 1 for 16 + 16 + 5 = $37.

Is there a way to do this with some sort of Do Until loop?

I've already solved this by hard coding the transformations with do until loops and if then statements, but I'd like to do it with a lookup tables so that I can change the prices later without having to change the program.

I have also figured out how to do this by just coping and pasting the merge code and repeating the data step over and over again. The problem there is I would have to guess what the maximum time I would ever need to do this would be. For example, with this case I would run the code three times. However, if a customer buys 17 widgets, I'd have to run the code 5 times.

Any thoughts? Thanks!

Re: How to use a lookup table using iterations?

PaigeMiller — Sun, 19 Jan 2020 23:41:58 GMT

Iterations don't seem necessary.

How about using math? If a customer buys 9, then 9 integer divide by 4 is 2, plus a remainder of 1. So the math is 2*(cost of 4) + (cost of 1). If your lookup table is an array which I shall creatively name COST, then for any quantity purchased n

if mod(n, dim(cost)) = 0 then total_cost = floor(n/dim(cost)) * cost(dim(cost));
else total_cost = floor(n/dim(cost)) * cost(dim(cost)) + cost(mod(n, dim(cost)));

Re: How to use a lookup table using iterations?

Patrick — Sun, 19 Jan 2020 23:31:59 GMT

Code using a lookup table could look like below.

data price;
  input quantity price;
  datalines;
1 5
2 9
3 13
4 16
;

data sample;
  input quantityBought;
  datalines;
9
25
5
;

data want(drop=_:);
  if _n_=1 then
    do;
      if 0 then set price;
      dcl hash h1(dataset:'price', ordered:'y', hashexp:4);
      dcl hiter hh1('h1');
      h1.defineKey('quantity');
      h1.defineData('quantity', 'price');
      h1.defineDone();
    end;

  set sample;
  _remain=quantityBought;
  _rc = hh1.last();
  do while (_rc = 0);
    _factor=int(_remain/quantity);
    if _factor>0 then
      do;
        PriceSum=sum(PriceSum, _factor*price);
        _remain=_remain-_factor*quantity;
        if _remain<=0 then leave;
      end;
    _rc = hh1.prev();
  end;
run;

proc print data=want;
run;

Re: How to use a lookup table using iterations?

BarryP — Sun, 19 Jan 2020 23:41:12 GMT

Hi Page, can you help me out with that a bit? I thought that maybe an "array" might help. I've taken the first 2 SAS programming classes and have about 2 years experience. I'm sure there's a new technique I need do delve into.

Can you give me a little more detail? Thanks!

FWIW, the way I would normally do this would be:

GIVEN TABLES:

Customer_Table:

Customer Num_items
A 1

B 2
C 3
D 4
E 9

Cost_Table

Num_items Cost
1 $5

2 $9

3 $13

4 $16
9 $37

SAS CODE:

Data New_Table;

merge Customer_Table Cost_Table;
by Num_items;

run;

The problem here, or course, is that I'd have to have a cost for every possible value for num_items.

Re: How to use a lookup table using iterations?

BarryP — Sun, 19 Jan 2020 23:44:16 GMT

Hmmm, this may be the way to do it. I don't understand all of your code, but I'm guessing there's something in there that pulls elements out of the price table and treats them like variables?

Re: How to use a lookup table using iterations?

Patrick — Sun, 19 Jan 2020 23:48:39 GMT

@BarryP wrote:

Hmmm, this may be the way to do it. I don't understand all of your code, but I'm guessing there's something in there that pulls elements out of the price table and treats them like variables?

Given that you're using SAS since two years it's likely worth that you skill-up with the SAS hash and hash iterator object.

Re: How to use a lookup table using iterations?

PaigeMiller — Sun, 19 Jan 2020 23:49:10 GMT

So data set Customer is as you show it.

Then

data want;
     array cost cost1-cost4 (5 9 13 16);
     set customer;
     if mod(num_items, dim(cost)) = 0 then total_cost = 
        floor(num_items/dim(cost)) * cost(dim(cost));
    else total_cost = floor(num_items/dim(cost)) * cost(dim(cost)) + 
        cost(mod(num_items, dim(cost)));
run;

Re: How to use a lookup table using iterations?

BarryP — Mon, 20 Jan 2020 00:04:14 GMT

Looking it up now. 😉

Re: How to use a lookup table using iterations?

mkeintz — Mon, 20 Jan 2020 04:39:37 GMT

To be honest, I don't see this single-product problem as a very good example of the benefits of hash, especially because the priced quantities are integers from 1 to 4. @PaigeMiller's code appears to be very compact for this problem, using an array indexed by the integers 1:4.

If what your are really looking for is having the prices in a separated data set, @PaigeMiller's code still works. It just has to be preceded by a proc transpose:

data prices;
  input cost;
datalines;
5
6
13
16
run;
proc transpose data=prices out=costs (drop=_name_) prefix=cost;
run;

data orders;
  input qty_ordered;
datalines;
5
25
19
run;

data want;
  set orders;
  if _n_=1 then set costs;
  array _prcs {*} cost:  ;
  total_cost=floor(qty_ordered/dim(_prcs))*_prcs{dim(_prcs)};
  if mod(qty_ordered,dim(_prcs))^=0 then total_cost=total_cost + _prcs{mod(qty_ordered,dim(_prcs))};
run;

However, I think using hash would be very educational if you had prices for multiple products ("A","B", etc.) up in the prices dataset. Then I could see enhancing @PaigeMiller's code with a hash, keyed on the product ID.

Code above changed per @PaigeMiller's observation.

Re: How to use a lookup table using iterations?

PaigeMiller — Mon, 20 Jan 2020 00:29:59 GMT

I agree with your comments about hash being unnecessary for the problem as stated.

Your code needs to be adjusted for the case where this quantity is equal to zero.

mod(qty_ordered,dim(_prcs))

Re: How to use a lookup table using iterations?

BarryP — Mon, 20 Jan 2020 02:00:55 GMT

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;

Re: How to use a lookup table using iterations?

unison — Mon, 20 Jan 2020 02:12:59 GMT

Take a look at this and let me know if this is aligned with what you're wanting:

data have;
	do num_items=1 to 50;
		output;
	end;
run;

data prices;
	input bulk bulk_price;
	datalines;
1 5
2 9
3 13
4 16
;
run;

proc sql noprint;
	select bulk_price, max(bulk) into 
		:price_list separated by ",", :num_prices from prices;
quit;

data want;
	set have;
	remainder=num_items;

	if num_items le &num_prices then
		price=1*scan("&price_list", num_items, ",");
	else
		do;
			array arr[&num_prices];
			do i=dim(arr) to 1 by -1;
				arr[i]=int(remainder/i);

				if remainder >=i*arr[i] and arr[i] > 0 then
					do;
						remainder=sum(remainder, -i*arr[i]);
						price=sum(price, arr[i]*scan("&price_list", i, ","));
					end;
			end;
		end;
	drop i arr: remainder;
run;

Re: How to use a lookup table using iterations?

mkeintz — Mon, 20 Jan 2020 05:18:26 GMT

@BarryP wrote:

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

So in this example are you saying that you have the following specific instrument translations available?

- written here as (A,B,C,D,E,F) (V,W,X,Y,Z):

(0,6,1,0,0,0) (0,1,0,3,1) From 0A, 6B, 1C, 0D, 0E, 0F to 0V, 1W, 0X, 3Y, 1Z (Used twice)

(0,1,1,0,0,0) (1,0,0,0,1) From 1B and 1C to 1V, 1Z (Used once)

What is the translation for the single remaining 1C? Is that the "0 instruments" line? I don't understand quite understand that.

Does it mean that for some customers, having a single "leftover" instrument of a particular type gets no translation (or officially translates to zero)? I would ordinarily assume that, in addition to some standard list of lookup combinations, there would always be a translation for each of the single original instruments Then you could account for leftovers.

In a way, this is a mathematical programming problem, where there may be more than one collection of translations that satisfy the customer order, but one would presumably choose the combination that minimizes the customer's cost - or optimizing some other criterion subject to the selected translations exactly satisfying the initial set of instruments.

Re: How to use a lookup table using iterations?

s_lassen — Mon, 20 Jan 2020 09:49:12 GMT

I assume you are looking for a general solution to this problem.

This is one of the cases where I think it may be advantageous to have a "broad" lookup table, e.g.:

data prices;
  input ItemCode n1-n5 price1-price5;
cards;
1 4 3 2 1 . 16 13 9 5 .
2 10 5 3 2 1 45.50 25 18 14 8
;run;

I put in an extra item with 5 prices, just for example. The ItemCode variable denotes the type of Item. Note that the numbers (n1-n5) and prices are arranged with the highest quantity first, and that the whole table is sorted by ItemCode.

Assuming your data is like this (again sorted by ItemCode, the InvoiceNo is just a hypothetical number identifying the invoice):

data sales;
input InvoiceNo ItemCode n;
cards;
13121 1 9
2133 1 14
13121 2 11
123123 2 25
;run;

You can calculate the "fair" price like this:

data TotalCost;
  merge sales(in=sale) prices;
  by ItemCode;
  if sale;
  array numbers n1-n5;
  array prices price1-price5;
  TotalCost=0;
  remain=n;
  do _N_=1 to dim(numbers) while(remain>0);
    TotalCost=TotalCost+int(remain/numbers(_N_))*prices(_N_);
    remain=remain-int(remain/numbers(_N_))*numbers(_N_);
    end;
  keep InvoiceNo ItemCode n TotalCost;
run;

If your quantities are whole numbers, and the last valid number in the numbers array is always 1, this should work.

The solution assumes that you cannot get a better price by splitting e.g. 5 items into 3+2 instead of 4+1.

Re: How to use a lookup table using iterations?

KachiM — Mon, 20 Jan 2020 10:43:16 GMT

Hi @BarryP ,

Here is one way using array to hold the prices for 0, 1, 2, 3 and 4 items. There are several answers and I have not examined all of them. Hope I am not repeating earlier answers.

data want;
   array k[0:4] _temporary_ (0 5 9 13 16);
   do items = 1 to 100;
      mul4 = int(items/4);
      price = mul4 * k[4] + k[mod(items,4)];
      output;
   end;
drop mul4;
run;

Re: How to use a lookup table using iterations?

PaigeMiller — Mon, 20 Jan 2020 11:55:31 GMT

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:

The same math works product by product that I provided above. Essentially, you do the same programming for each instrument, and then add things up. Perhaps there are smarter or faster ways to do this, but I still don't see a need for any looping or iterations within a product. You could write a loop that goes over all products.

Re: How to use a lookup table using iterations?

BarryP — Mon, 20 Jan 2020 13:35:17 GMT

Yes, you understand it correctly.

I find the 0 translation weird, too, but I don't work in sales. I just have to use the numbers that they give me.

Re: How to use a lookup table using iterations?

BarryP — Mon, 20 Jan 2020 15:27:57 GMT

Hi all,

So as I'm working through this it seems like the key is that in order to treat the elements of a lookup table like variables, I need to put them into an array. Is that correct?

So, for example if I have a sale, 1 item for $4, or 4 items for $12, if I buy 5 items then I would need to take the price for 4 items, subtract the 4 items from the total, and then add the price for the 1 item, subtract that 1 item from the total, and now that I'm at zero items, I leave the loop.

Sorry, I've never worked with arrays in SAS before. So I'm assuming that to use an array like a lookup, its always going to look for the i'th element. So instead of thinking "lookup the element that corresponds with sale_scenario_4," I have to think, "lookup the 4th element in the array and make sure that you have the value for sale-scenario_4 there."

Is that right?

Re: How to use a lookup table using iterations?

mkeintz — Mon, 20 Jan 2020 16:18:39 GMT

I see two ambiguous issues in the task you describe:

Single instrument issue: Your example shows pricing levels for every quantity from 1 to 4 (or more generally from 1 to Np - the maximum quantity discounted for product p). Because it covers every quantity in the 1:Np interval, it's a perfect fit for the array approach, and your description "lookup the 4th element in the array and make sure that you have the value for sale-scenario_4 there" is spot on.

But it may be that you have discount breaks not from 1 to 4, but at 1, 2, 5, and 10. Then the 4th element is not for quantity 4 but for quantity 10. One can work around this while still using the array approach.
Mixed-instrument translations. As I understand it, you have a lookup table in which various (non-exhaustive) mixes of instruments A,B,C,D will be replaced with some mix of new instruments W,X,Y,Z. This can be more of a problem, because if your customer doesn't have any of the exact translations you have in the lookup, you might not arrive at unique mix of new instruments.

For instance, what if your initial lookup table of {A,B} => {W,X} available translations are (admittedly contrived)
     {6,2} =>     {2,1}
     {2,6} =>     {1,2}
     {0,2} =>     {0,1}
     {2,0} =>     {1,0}

If the customer starts out with {A,B}={6,6}, then you could end up with either
    {W,X} = {2,1} + {0,1} + {0,1} == {2,3}     or
    {W,X} = {1,2} + {1,0} + {1,0} == {3,2}

You would need a way to decide between these two alternatives.
Also in your mixed-instrument lookup table, do those mixes of new instruments also have quantity discounts that do not match what you would produce by applying the instrument-by-instrument discounts in the new mix?

Re: How to use a lookup table using iterations?

BarryP — Mon, 20 Jan 2020 18:12:52 GMT

I think you are understanding me correctly.

In a nutshell I have ~100 unique old instrument combinations that will translate to new instrument combinations. If that was all I had, it would be an easy merge. I need to account for, however, combinations outside of that list (the list is a table that was provided to me from marketing).

So for example, I'll lay out the first 8 elements and codes that correspond to the instrument combinations:

Code Inst_A Inst_B
1-A1 1 0

1-A1B1 1 1
1-A2 2 0
1-A2B1 2 1
1-A3 3 0
1-A4 4 0
1-A5 5 0
1-A6 6 0

The translations are the following:

Code InstW InstX InstY InstZ

1-A1 1 0 0 1

1-A1B1 1 0 0 1
1-A2 0 1 0 2
1-A2B1 0 1 0 2
1-A3 0 1 2 0
1-A4 0 1 2 1
1-A5 0 1 3 0
1-A6 0 1 4 0

So if I have, for example, 7 Inst_A, the translation would be 1-A6 + 1-A1 which =

Code InstW InstX InstY InstZ

1-A6 + 1-A1 1 1 4 1

So I think I've resigned myself to the fact that the algorithms that tell you what InstA/InstB combinations are going to have to be hard coded, particularly when dealing with what order to do the calculations in, but I believe that simple 1 dimensional arrays can be used here. (???) Perhaps just calculate W, X, Y, & Z separately. The key here is that I've either done these as table merges or hard coding if/then statements with the math written into the code.

I want to be able to utilize an imported excel table as much as possible. If needed, I can even add a column that just defines the i'th element in the lookup table.