Using matrix as lookup table

[Mohana Mondal]

Hello,

I have two datasets. I am trying to get a new variable in the second dataset, which looks up the value from the dataset1.

The steps that I have to follow are:-

1) Make a matrix for dataset1

2) Compare the randomnumber value(in dataset2), to the values under the columns origin* in the matrix, so that, STATA starts looking at the column starting with the same origin* number as the oldau values in dataset2.

For example, if I take randomnumber value .955492, it should start looking in the dataset 1, for values under origin12, as this is what the corresponding oldau is in dataset2,

If I take randomnumber .018467158 then, it should start looking in the dataset1, for values under origin13, as this is what the corresponding oldau is in dataset2 and so on.

3) I have to then identify the value under the origin* columns, which is the immediate bigger than the randomnumber.

In the first example, it is 1 (under origin12)
In the second example, it is 0.09 (under origin13)

4)Then, I have to generate a new variable which corresponds to the bigger value identified, which is under the destination column in the dataset1.

In the first example, I have to get the return value 99, as the destination value corresponding to 1, is 99.
In the second example, I have to get the return value 11, as the destination value corresponding to 0.09, is 11.

5) I have to get all the return values in one new variable.




Dataset 1

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte destinationau float(origin11 origin12 origin13 origin14)
11   0 .28 .09 .07
12 .34 .28 .33 .26
13 .56 .59 .33 .81
14 .66 .73  .7 .81
99   1   1   1   1
 .   .   .   .   .
end

Dataset 2

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(oldau newau) float randomnumber
11 11    .795571
12  0    .955492
13  0 .018467158
14 14 .030028736
12  0   .5734589
12  0   .9656307
end

Any help will be appreciated.

[Mike Lacy]

I don't understand very well what you want, but a good first step for you would be to consult -help merge-, which is the command used in Stata for something like a lookup table. This may get you started. Perhaps someone else will understand better than me the rest of what you mean.