[Hemanshu Kumar]

To bring in the labels:

sort year
forval sec = 1/11 {
    by year: egen _wanted_`sec' = mean(avg_tuition_fees_ft) if sector==`sec'
    by year: egen wanted_`sec' = max(_wanted_`sec')
    local lab: label sectordf `sec'
    label var wanted_`sec' `"`lab'"'
}
drop _wanted*

[Nick Cox]

I've looked at #13 to #15 but failed to understand what you're asking. Sorry. But what you are talking about seems to keep changing too. For example the number of sectors seemed to be 9 but was 11 in one post. Whatever you did in MS Excel is even harder to comment on than what you did in Stata, meaning impossible.

Although it's not much of an answer, I note very generally that working with different versions of the same dataset could be confusing.

More specifically,

1. Stata will accept unambiguous abbreviations of variable names, unless you switch that off.

2. The wildcard sector_? won't catch e.g. sector_10 as the ? is looking for any single character.

[Rocky Christensen]

Nick Cox and Hemanshu Kumar thank you so much for all your help! I apologize that I never confirmed that everything was working for me. I noticed this as I circled back with my current dillema.

The solutions you offered did work, but I need to add one more layer of nuance to the code. I need to adjust the values per sector to account for a weighted average of prices per state. I want to weight in-state at 64% and out-of-state at 35%.


This code has worked for me so far:

sort year
forval sec = 1/6 {
by year : egen _sector_`sec' = mean(avg_tuition_fees_ft) if sector==`sec'
by year : egen sector_`sec' = max(_sector_`sec')
local lab: label sectordf `sec'
label var sector_`sec' `"`lab'"'
}
drop _sector*


*****************Rename******************

rename sector_1 Public_4yr_or_above
rename sector_2 Private_NFP_4yr_or_above
rename sector_3 Private_FP_4yr_or_above
rename sector_4 Public_2yr
rename sector_5 Private_NFP_2yr
rename sector_6 Private_FP_2yr

As I consider how to account for the averages on a per-state basis my best assessment is to simply add state like the following and then this would put the value per state per year with each college throughout my data set.

For example, Anywhere University Arizona 2008 would have the same value as Unknown University Arizona 2008. However, Questionable University Arkansas 2008 would have a different average since it is a different state even though it is the same year.

This is the code I was thinking would work:

sort year state
forval sec = 1/6 {
by year state : egen _sector_`sec' = mean(avg_tuition_fees_ft) if sector==`sec'
by year state : egen sector_`sec' = max(_sector_`sec')
local lab: label sectordf `sec'
label var sector_`sec' `"`lab'"'
}
drop _sector*


*****************Rename******************

rename sector_1 Public_4yr_or_above
rename sector_2 Private_NFP_4yr_or_above
rename sector_3 Private_FP_4yr_or_above
rename sector_4 Public_2yr
rename sector_5 Private_NFP_2yr
rename sector_6 Private_FP_2yr

This unfortunately is not working and I am not sure how to account for the weighted average I want for each college in each state per year. I would be most grateful for some guidance in this regard.