Changing standings of a clasification based on some criteria

[Francisco Carballo]

Dear Statalist,

This is another question about nested loops (maybe there is another way).

Let's say we have the data of the ranking positions of the past 4 FIFA World Cups. However and unfortunately, some countries have been desquilified and their results in the past tournaments have been taken out. So we need to give the titles (and 2nd and 3rd positions) to the next in the ranking.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str11 (rank1 rank2 rank3 rank4 rank5 rank6)
2018 "Germany" "Brazil"      "Argentina"   "Spain"    "France"    "Sweden"  
2014 "Germany" "Argentina"   "Netherlands" "Brazil"   "Colombia"  "Belgium"  
2010 "Spain"   "Netherlands" "Germany"     "Uruguay"  "Argentina" "Brasil"  
2006 "Italy"   "France"      "Germany"     "Portugal" "Brasil"    "Argentina"
end

Germany, Argentina and Spain have been disqualified and lose their titles, second, and third positions. So they are removed and we want to know how the standings look like after they are taken out (moving the rest of the teams up). For example, the changes for 2006 would be Italy1st, France 2nd and Portugal 3rd (we don't care about 4th, 5th and 6th position changes)

Any ideas? I have been playing around with nested loops but I have not gotten what I want.

[Clyde Schechter]

No loops needed at all. The problem is simple once you reorganize the data into long layout:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year str11 (rank1 rank2 rank3 rank4 rank5 rank6)
2018 "Germany" "Brazil"      "Argentina"   "Spain"    "France"    "Sweden"  
2014 "Germany" "Argentina"   "Netherlands" "Brazil"   "Colombia"  "Belgium"  
2010 "Spain"   "Netherlands" "Germany"     "Uruguay"  "Argentina" "Brasil"  
2006 "Italy"   "France"      "Germany"     "Portugal" "Brasil"    "Argentina"
end

rename rank* country*
reshape long country, i(year) j(rank)

drop if inlist(country, "Germany", "Argentina", "Spain")  & inlist(rank, 1, 2, 3)
by year (rank), sort: replace rank = _n

reshape wide country, i(year) j(rank)

Note: Nearly all analyses in Stata are easier with the data arranged in long layout. Many aren't even possible in wide layout. So probably you should skip the final -reshape wide- command and leave the data in long layout. But I put that command there in case there is nothing further to be done and you want the data arranged in a way that is easier for human eyes to grasp.

[Francisco Carballo]

Thank you Clyde. Helpful as always.

This was a made-up example in order to make my problem more understandable in Statalist. I just realised that my approach was the most complicated one 5 seconds after posting this simplified version.

[Francisco Carballo]

Just in case this post might be helpful for someone else:

No loops needed at all. The problem is simple once you reorganize the data into long layout:

drop if inlist(country, "Germany", "Argentina", "Spain") & inlist(rank, 1, 2, 3)

Your previous code (before editing it) was the right one. If we include the last part of this line, it will not drop these countries when they finished the World Cup in 4th, 5th or 6th. Being then possible for some of them to make it up to 3rd and we do not want that.

[Clyde Schechter]

Your previous code (before editing it) was the right one. If we include the last part of this line, it will not drop these countries when they finished the World Cup in 4th, 5th or 6th. Being then possible for some of them to make it up to 3rd and we do not want that.

Thanks for catching that and fixing it.