Create a new variable based on another variable by year and region

[David Wang]

Hello All,

I wanted to create a new variable score1, which equals the value of the variable score when variable rank=1 within that year and region. I have been trying different ways, but no success. Here are my unsuccessful codes:

by year region, sort: gen score1 = score if rank==1

This line of codes generates missing values. For the score1, what I want to achieve is to have 0.22 for the entire year 2016 and region 1; and 0.51 for the entire 2016 and region 2. I am not a Stata savvy. I cannot figure that out. Any help is much appreciated!

*To install: 
ssc install dataex 
clear 
input int year int region float score int rank
2016    1    0.22    1
2016    1    0.18    2
2016    1    0.15    3
2016    2    0.51    1
2016    2    0.42    2
2016    2    0.41    3
2015    1    0.98    1
2015    1    0.67    2
2015    1    0.17    3
2015    2    0.72    1
2015    2    0.66    2
2015    2    0.60    3
2014    1    0.59    1
2014    1    0.52    2
2014    1    0.36    3
2014    2    0.65    1
2014    2    0.60    2
2014    2    0.50    3

end

Many thanks,
David

[Clyde Schechter]

isid year region rank, sort
by year region: egen score1 = max(cond(rank == 1), score, .)

Note: The -isid year region rank- condition is a little bit of overkill here. All that is really needed is that each combination of year and region have only a single observation with rank == 1. But verifying that is more complicated, and the example data suggests that in fact year region and rank uniquely identify observations--so I opted for the easier verification of that condition.

If this code breaks in your real data on the -isid- command, post back and I'll write the longer code needed for the less stringent condition of only one observation with rank == 1 per year-region group. If you do that, please explain the possible values that variable rank can take.

[David Wang]

Hi Clyde,

Thank you so much! Your codes worked perfectly for my real data!

Best regards,
David