Producing a table for panel data with most recent observation

[Imed Limam]

Dear all,
I have an unbalanced panel data for a number of countries on the percentage of youth not in education, employment and training % (neet). The neet variable for each country and year is reported by sex (male, female, total). So, for any country at any given year, neet is reported for males, females and for both sexes (tot). I would like to produce a table that gives for each country the values of neet (male, female, total) for the most recent year , which is not the same for all the countries. I have tried the following code, which gives the results only sequentially, i.e. one sex category at a time. As I am sure there is a more efficient way of doing that. I would appreciate any suggestion in that regard. I am using Stata 14.2.

drop if missing(neet)
local var tot male female
foreach x of local var {
preserve 
keep if sex == "`x'"
bysort country (year) : keep if _n == _N 
bys country (year) : egen `x'_neet = total(cond(sex == "`x'", neet, .)) // retaining neet for specific sex
local arneet "`arneet' `x'_neet"
list country year `x'_neet
restore
}

Here is a chunk of the data. N.B. The original variable referring to years is a string variable (time) that needs to be encoded.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 ccode str33 country str6 sex str4 time double neet long year
"DZA" "Algeria"    "tot"    "2009" 25.45 34
"DZA" "Algeria"    "male"   "2009" 11.23 34
"DZA" "Algeria"    "female" "2009" 39.93 34
"DZA" "Algeria"    "tot"    "2010" 24.53 35
"DZA" "Algeria"    "male"   "2010" 10.76 35
"DZA" "Algeria"    "female" "2010" 39.06 35
"DZA" "Algeria"    "tot"    "2011"    26 36
"DZA" "Algeria"    "male"   "2011"    15 36
"DZA" "Algeria"    "female" "2011"  37.4 36
"DZA" "Algeria"    "tot"    "2012"  22.7 37
"DZA" "Algeria"    "male"   "2012"  10.8 37
"DZA" "Algeria"    "female" "2012"  34.8 37
"DZA" "Algeria"    "tot"    "2013"  21.5 38
"DZA" "Algeria"    "male"   "2013"   8.8 38
"DZA" "Algeria"    "female" "2013"  34.6 38
"DZA" "Algeria"    "tot"    "2014"  22.8 39
"DZA" "Algeria"    "male"   "2014"  11.4 39
"DZA" "Algeria"    "female" "2014"  34.7 39
"DZA" "Algeria"    "tot"    "2015"  21.2 40
"DZA" "Algeria"    "male"   "2015"  10.8 40
"DZA" "Algeria"    "female" "2015"  32.1 40
"DZA" "Algeria"    "tot"    "2017" 20.95 42
"DZA" "Algeria"    "male"   "2017" 10.94 42
"DZA" "Algeria"    "female" "2017" 31.69 42
"COM" "Comoros"    "tot"    "2004" 27.89 29
"COM" "Comoros"    "male"   "2004" 22.73 29
"COM" "Comoros"    "female" "2004" 32.61 29
"COM" "Comoros"    "tot"    "2014" 27.58 39
"COM" "Comoros"    "male"   "2014" 21.35 39
"COM" "Comoros"    "female" "2014" 33.13 39
"DJI" "Djibouti"   "tot"    "2017" 19.32 42
"DJI" "Djibouti"   "male"   "2017" 14.51 42
"DJI" "Djibouti"   "female" "2017" 24.05 42
"EGY" "Egypt"      "tot"    "2008" 29.66 33
"EGY" "Egypt"      "male"   "2008" 15.68 33
"EGY" "Egypt"      "female" "2008" 46.93 33
"EGY" "Egypt"      "tot"    "2009"  29.9 34
"EGY" "Egypt"      "male"   "2009"  16.2 34
"EGY" "Egypt"      "female" "2009" 47.01 34
"EGY" "Egypt"      "tot"    "2010" 33.08 35
"EGY" "Egypt"      "male"   "2010"  15.8 35
"EGY" "Egypt"      "female" "2010" 52.01 35
"EGY" "Egypt"      "tot"    "2011"  32.1 36
"EGY" "Egypt"      "male"   "2011" 17.95 36
"EGY" "Egypt"      "female" "2011" 48.47 36
"EGY" "Egypt"      "tot"    "2012" 31.55 37
"EGY" "Egypt"      "male"   "2012" 19.61 37
"EGY" "Egypt"      "female" "2012" 45.67 37
"EGY" "Egypt"      "tot"    "2013"  28.4 38
"EGY" "Egypt"      "male"   "2013" 17.92 38
"EGY" "Egypt"      "female" "2013" 41.11 38
"EGY" "Egypt"      "tot"    "2015" 27.61 40
"EGY" "Egypt"      "male"   "2015" 19.76 40
"EGY" "Egypt"      "female" "2015" 35.81 40
"EGY" "Egypt"      "tot"    "2016" 27.61 41
"EGY" "Egypt"      "male"   "2016" 19.88 41
"EGY" "Egypt"      "female" "2016" 35.69 41
"EGY" "Egypt"      "tot"    "2017"  26.9 42
"EGY" "Egypt"      "male"   "2017" 19.58 42
"EGY" "Egypt"      "female" "2017"    35 42
"EGY" "Egypt"      "tot"    "2018" 27.09 43
"EGY" "Egypt"      "male"   "2018"  18.6 43
"EGY" "Egypt"      "female" "2018"  36.6 43
"EGY" "Egypt"      "tot"    "2019" 27.96 44
"EGY" "Egypt"      "male"   "2019" 16.44 44
"EGY" "Egypt"      "female" "2019" 40.34 44
"EGY" "Egypt"      "tot"    "2020" 30.19 45
"EGY" "Egypt"      "male"   "2020"  17.2 45
"EGY" "Egypt"      "female" "2020" 43.97 45

[Øyvind Snilsberg]

one way to do this,

bysort country (year): gen recent = year[_N]
version 14: table country sex if year == recent, c(mean neet)

[Clyde Schechter]

Your data contains two variables, year and time, and, apart from the fact that year is a string variable, they are synchronized, with time = 1975 + year. A string variable for year is unlikely to be useful in Stata. And a year variable that is offset is also somewhat odd to work with, though feasible. So I'm going to destring the year variable and work with that.

destring time, replace
sort country sex time
collapse (last) neet time, by(country sex)

[Clyde Schechter]

Just noticed that in your code you try to get the output with the three neet variables aligned horizontally. If you do the code I show in #3 and follow it with:

rename neet _neet
reshape wide @_neet, i(country time) j(sex) string

you will get that layout.

[Imed Limam]

Many thanks Øyvind and Clyde for your help and prompt response. Both suggestions work fine. All the best.