Vlookup in Stata

[Mohana Mondal]

Hello,
There are four regions (Area Unit), and four people.

The first table is the origin-destination matrix, which is the probabilities that a mover who started in the origin will end in the destination. The second table cumulates the values in each column of the first table.


Table 1
Origin AU
Destination 11 12 13 14
NULL 0.00 0.00 0.00 0.00
11 0.00 0.28 0.09 0.07
12 0.34 0.00 0.24 0.19
13 0.22 0.31 0.00 0.55
14 0.10 0.14 0.37 0.00
Outside 0.34 0.27 0.30 0.19



Table 2

Origin AU
Destination AU 11 12 13 14
NULL 0.00 0.00 0.00 0.00
11 0.00 0.28 0.09 0.07
12 0.34 0.28 0.33 0.26
13 0.56 0.59 0.33 0.81
14 0.66 0.73 0.70 0.81
Outside 1.00 1.00 1.00 1.00


The table at the bottom tracks what happens to the four people. It omits the first step, where we identify which people are movers. It simply jumps to the step where person #2 and #3 are movers, and the other two are stayers. It then looks up, for the movers, where they move to, using the random number in the fourth column.

Table 3
Old AU New AU Random Number New AU (after movers) {Result}
Person 1 11 11 0.795571004 11
Person 2 12 0 0.955491993 Outside
Person 3 13 0 0.018467159 11
Person 4 14 14 0.030028737 14



I am using the IF(C16=0,INDEX($G$5:$G$10,MATCH(D16,$I$5:$I$10)+1) ,B16) function in Excel. I want to know if there is a way in Stata I can do it. The Second table has the range of values that I specify as G and I. I am attaching a snapshot of the excel spreadsheet that I have.


Any help will be appreciated,
Thank you

[Maarten Buis]

So you try to follow a person through a Markov chain with an absorbing state. Actual computations are much easier when you just multiply matrices, but it can be instructive to follow a hypothetical person.

Here is one way to do so. The most useful implementation depends on what you exactly want to do with this, so there is no guarantee that this is the best way to do this for you.

matrix trans = ///
0.00, 0.28, 0.09, 0.07 \ ///
0.34, 0.00, 0.24, 0.19 \ ///
0.22, 0.31, 0.00, 0.55 \ ///
0.10, 0.14, 0.37, 0.00 \ ///
0.34, 0.27, 0.30, 0.19

// I like my transition matrices to be rows = origins, cols = destinations
matrix trans = trans'

// person 1 started in state 1
local current = 1
local trajectory1 = 1
while `current' != 5 {  // as long as person has not reached the absorbing state
    local u = runiform()
    local cumul = 0
    forvalues i = 1/5 {
        local cumul = `cumul' + trans[`current', `i']
        if `u' < `cumul'{
            local current = `i'
            continue, break
        }
    }
    local trajectory1 = "`trajectory1', `current'"
}
di "`trajectory1'"

[Mohana Mondal]

Thank you so much. Can you please tell me why you are using #=!5 in #
while `current' != 5? When I try to run it it says, =5 invalid name. BAsically I am trying to assign geographic areas (AU in this case) based on the random numbers. If the proportions in the cumulative OD matrix are greater than the random numbers, then the person moves, hence we assign a new AU to that person based on the AU they recorded in the origin and the random numbers.

[Maarten Buis]

To run code like that you need to copy it in its entirety to the do-file editor and run it all in one go. In that case you should not get an error. Instead it should show you the trajectory of a random person starting in state 1 until it reached state 5 (the absorbing state).

Apparently this is not what you want to do. It is still not quite clear to me what it is that you do want to do. The devil with these kinds of tasks is in the detail, so whenever you use words like "basically", then that is a clear sign that there is insufficient information. You need to specify exactly the rule you want to implement, the exact structure of the data you start with, and the exact structure of the data you want to end with. Did I mention the word exactly? That is important!

However, I can give you two pieces of advise: Store those tables in matrices, and you can refer to the elements by row and column number. All you need to do with matrices is documented in help matrix . You may also want to look at https://www.stata-journal.com/articl...article=pr0032 . Elements of the code I gave you in #2, together with that article, and the proper use of matrices should be enough to solve problems of that nature.

[Mohana Mondal]

Hi Maarten,

I have been studying about matrices. But I am still unable to figure out a way to do what I want.


I will try and explain my problem clearly.

For person2, Old AU is 12. I have to set up a matrix for the Table1(origin-destination) in such a way, that STATA looks up the random number 0.955492, in the origin-destination matrix, but starts looking up for the values under column origin AU 12, as that is what its oldau is in Table 2. If we take person 3, STATA should look up the random number value .018467, in the OD matrix, but only under the column origin AU13, as that is what the oldAU is in table 3.
After looking up for the random values, I want to get the next number under that column which is higher than the random number. In this case, for person 2, next higher number after random number 0.955492, under column originAU12 is 1. So I want return value 1.

Is there a way to do this?