Using value of variable to determine which variable is used in a function

[Stephanie Stewart]

Hello and sorry if this has been asked elsewhere; I couldn't seem to find the right way to phrase my question.

Basically, I am interested in calculating the per capita funding received by a county for a specific project. For each project, I have information on the project fiscal year. I also have the county population for all years over which projects were funded. The population for each year is stored under the variables pop1980, pop1981, pop1982, etc. What I would like to do is use the project fiscal year to determine which population variable is used to calculate the per capita value. So, for example, if a project was conducted in FY 2001, then I would like for the per capita project value to be calculated as project_cost/pop2021. I don't know if there is a way to do this in a single command. In my mind it would be something like

gen per_cap_cost = project_cost/pop[the output of programfy for that observation]

, but I don't know if this is overly simplified. I've attached the data for reference.

Thanks!

[Rich Goldstein]

many of us will not open, or even download, binary files from people we don't know; the FAQ explains exactly the preferred method for showing sample data; please read the FAQ and follow its advice

[Nick Cox]

The dataset is not as you outline. I can't find a variable programfy but I can see


population_1980 population_2005_2009 population_2009_2013 population_2013_2017
population_1990 population_2006_2010 population_2010_2014 population_2014_2018
population_2000 population_2007_2011 population_2011_2015 population_2015_2019
population_2010 population_2008_2012 population_2012_2016 population_2016_2020

What are your rules? For example, which population variable should you use if a project was in 2013?

If the data were as you describe. here is one approach

gen project_cost = .  

forval y = 1980/2020 { 
      replace per_cap_cost = project_cost / pop`y' if programfy == `y' 
}

[Stephanie Stewart]

Apologies, I have been told to include my data before and I assumed that meant as an attachment. I also realized I included an older version of the dataset. I've displayed the updated the data below for clarity. Hopefully I have correctly understood what is advised by the FAQs.

* Example generated by -dataex-. For more info, type help dataex
clear
input int programfy long(pop1989 pop1990 pop1991)
2009 33996 34356 35018
2008 33996 34356 35018
2004 33996 34356 35018
2004 33996 34356 35018
2009 33996 34356 35018
2004 33996 34356 35018
2009 33996 34356 35018
1990 33996 34356 35018
2003 33996 34356 35018
2010 33996 34356 35018
end

Luckily, it seems that my description was enough for Nick to answer the question, so thank you Nick.

[Nick Cox]

The first line in #3 should have been

 
 gen per_cap_cost = .

I hope that was moderately obvious.