Look up an observation in one variable and return an observation from another variable

[Urgendalai Boldsukh]

Hello,

I have a dataset that contains four variables and millions of observations. I extracted three observations as an example.

id	year	tot_popc	weig_sum
bh380	2021	4043308	14687
co712	1950	3624225	9852
af301	1980	4085501	29461

I want to look up the id names and then automatically use values from variables tot_popc and weig_sum to do calculations.

The following code is not correct to the Stata but a similar structure is the goal which is to call the id name at the first line and the bolded lines give back to the values instead of manually typing there.


if id == "af301" {

double tot_popc = 4085501.00
double weig_sum = 29461.00

gen popwt = weig_sum / tot_popc

if (popwt < 0) {
popwt = 0
}
popwt = round(popwt)
}


I am still a newbie to Stata and appreciate any suggestions and ideas. Thank you very much!

[Clyde Schechter]

The most I can glean from your post is that you want to merge values of the totpc and weig_sum variables in the data set that you showed a few observations of into some other context. But you give no other information about that other context; and without understanding that, it is not possible to answer your question.

I am also perplexed how your first data set can contain millions of observations. In order for the operation you want to undertake to be meaningful, each country must be mentioned only once in that data set: otherwise, how will your computer know which values of totpc and weig_sum to use if, say "afghanistan" appears more than once. As there are fewer than 200 countries in the world, I don't see how that data set can have more observations than that and be fit for purpose.

So clearly I am seriously misunderstanding your data or how you want to use it, or both. Please clarify.

[Urgendalai Boldsukh]

The most I can glean from your post is that you want to merge values of the totpc and weig_sum variables in the data set that you showed a few observations of into some other context. But you give no other information about that other context; and without understanding that, it is not possible to answer your question.

I am also perplexed how your first data set can contain millions of observations. In order for the operation you want to undertake to be meaningful, each country must be mentioned only once in that data set: otherwise, how will your computer know which values of totpc and weig_sum to use if, say "afghanistan" appears more than once. As there are fewer than 200 countries in the world, I don't see how that data set can have more observations than that and be fit for purpose.

So clearly I am seriously misunderstanding your data or how you want to use it, or both. Please clarify.

Thank you for your reply and suggestions.
The country column actually is id numbers, I made up those countries. I will correct the sample to better understand. Sorry for the confusion.

Given all observations are unique as you noticed, the calculation will not be about merging values.

[Clyde Schechter]

Well you have cleared up the mystery of the size of the data set you show. But you still have not explained the context into which you want to bring tot_popc and weig_sum.

I will assume that you have some other Stata data set that is to receive these variables and that it, too, contains the id variable. Then you can do this:

use second_data_set, clear
merge m:1 id using first_dataset, keep(match master)
gen popwt = round(max(0, weig_sum/tot_popc))

[Joro Kolev]

You can also check out the user written command -vlookup-.

You type in Stata -findit vlookup- and you follow instructions to install.