Reshape ILO data

[Budu Gulo]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str55 Country str244 Sex str11 Age str31 Education str22 Survey str19 Type_of_Source str17(y1998Q1 y1998Q2 y1998Q3) str6 y1998Q4 str17(y1999Q1 y1999Q2 y1999Q3 y1999Q4)
"Portugal" "Total (Sex)" "15+ (Age)"   "Basic (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "6.1"  "4.8"               "4.6"               "4.7"  "4.8"               "4.6"               "4.2"               "4.3"              
"Portugal" "Total (Sex)" "15+ (Age)"   "Advanced (Aggregate levels)"     "EU Labour Force Survey" "Labour force survey" "3.7"  "2.8"               "4.1"               "3.6"  "3.4"               "3.3"               "4.7"               "4"                
"Portugal" "Total (Sex)" "15+ (Age)"   "Intermediate (Aggregate levels)" "EU Labour Force Survey" "Labour force survey" "7.8"  "5.8"               "7.1"               "7.4"  "7.1"               "6"                 "5.3"               "4.9"              
"Portugal" "Total (Sex)" "15+ (Age)"   "Total (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "6.1"  "4.7"               "4.8"               "4.9"  "4.9"               "4.6"               "4.4"               "4.4"              
"Portugal" "Total (Sex)" "25+ (Age)"   "Advanced (Aggregate levels)"     "EU Labour Force Survey" "Labour force survey" "2.9"  "2.6"               "3.1"               "2.6"  "2.4"               "2.4"               "4"                 "3.5"              
"Portugal" "Total (Sex)" "25+ (Age)"   "Basic (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "5.1"  "4"                 "3.9"               "4"    "4"                 "3.9"               "3.6"               "3.7"              
"Portugal" "Total (Sex)" "25+ (Age)"   "Intermediate (Aggregate levels)" "EU Labour Force Survey" "Labour force survey" "6.1"  "4.5"               "5"                 "4.9"  "5.5"               "5.2"               "3.9"               "3.7"              
"Portugal" "Total (Sex)" "25+ (Age)"   "Total (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "5"    "3.9"               "3.9"               "3.9"  "4"                 "3.9"               "3.7"               "3.7"              
"Portugal" "Total (Sex)" "15-24 (Age)" "Advanced (Aggregate levels)"     "EU Labour Force Survey" "Labour force survey" "13.2" "4.9"               "16.1"              "14.4" "14.6"              "14.4"              "14.9"              "9.699999999999999"
"Portugal" "Total (Sex)" "15-24 (Age)" "Basic (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "11.7" "9.6"               "9.199999999999999" "9.6"  "9.4"               "8.9"               "8"                 "8.199999999999999"
"Portugal" "Total (Sex)" "15-24 (Age)" "Intermediate (Aggregate levels)" "EU Labour Force Survey" "Labour force survey" "13.2" "10.3"              "14.4"              "15.4" "12.8"              "9.1"               "10.1"              "8.9"              
"Portugal" "Total (Sex)" "15-24 (Age)" "Total (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "12"   "9.5"               "10.4"              "10.9" "10.3"              "9.199999999999999" "8.699999999999999" "8.4"              
"Portugal" "Total (Sex)" "15-64 (Age)" "Advanced (Aggregate levels)"     "EU Labour Force Survey" "Labour force survey" "3.7"  "2.8"               "4.2"               "3.7"  "3.4"               "3.4"               "4.8"               "4"                
"Portugal" "Total (Sex)" "15-64 (Age)" "Basic (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "6.4"  "5"                 "4.8"               "5"    "5"                 "4.8"               "4.4"               "4.6"              
"Portugal" "Total (Sex)" "15-64 (Age)" "Intermediate (Aggregate levels)" "EU Labour Force Survey" "Labour force survey" "7.8"  "5.8"               "7.1"               "7.4"  "7"                 "6"                 "5.3"               "4.9"              
"Portugal" "Total (Sex)" "15-64 (Age)" "Total (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "6.3"  "4.9"               "5"                 "5.2"  "5.1"               "4.8"               "4.6"               "4.5"              
"Portugal" "Male (Sex)"  "15+ (Age)"   "Advanced (Aggregate levels)"     "EU Labour Force Survey" "Labour force survey" "3"    "2.2"               "3"                 "2.8"  "2.5"               "3.6"               "3.7"               "3.4"              
"Portugal" "Male (Sex)"  "15+ (Age)"   "Basic (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "4.9"  "3.9"               "3.6"               "3.8"  "4.1"               "4.2"               "3.9"               "3.8"              
"Portugal" "Male (Sex)"  "15+ (Age)"   "Intermediate (Aggregate levels)" "EU Labour Force Survey" "Labour force survey" "6.2"  "4.5"               "5.5"               "5.7"  "5.2"               "4.8"               "4.4"               "3.8"              
"Portugal" "Male (Sex)"  "15+ (Age)"   "Total (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "4.9"  "3.9"               "3.8"               "4"    "4.1"               "4.2"               "3.9"               "3.8"              
"Portugal" "Male (Sex)"  "25+ (Age)"   "Advanced (Aggregate levels)"     "EU Labour Force Survey" "Labour force survey" "2.5"  "1.8"               "2.4"               "2.3"  "2"                 "3"                 "3.4"               "3.5"              
"Portugal" "Male (Sex)"  "25+ (Age)"   "Basic (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "4.2"  "3.2"               "3"                 "3.2"  "3.4"               "3.7"               "3.5"               "3.4"              
"Portugal" "Male (Sex)"  "25+ (Age)"   "Intermediate (Aggregate levels)" "EU Labour Force Survey" "Labour force survey" "5"    "3.4"               "4.5"               "3.9"  "4"                 "4.2"               "3.3"               "2.8"              
"Portugal" "Male (Sex)"  "25+ (Age)"   "Total (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "4.1"  "3.1"               "3.1"               "3.2"  "3.3"               "3.7"               "3.5"               "3.3"              
"Portugal" "Male (Sex)"  "15-24 (Age)" "Advanced (Aggregate levels)"     "EU Labour Force Survey" "Labour force survey" "12.9" "9.300000000000001" "13.6"              "12.5" "13.6"              "18"                "6.8"               "2.7"              
"Portugal" "Male (Sex)"  "15-24 (Age)" "Basic (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "9.1"  "8.300000000000001" "7.3"               "7.5"  "8.300000000000001" "7.5"               "6.2"               "6.6"              
"Portugal" "Male (Sex)"  "15-24 (Age)" "Intermediate (Aggregate levels)" "EU Labour Force Survey" "Labour force survey" "10.7" "9.199999999999999" "10"                "13.3" "10.2"              "7.4"               "9.199999999999999" "7.4"              
"Portugal" "Male (Sex)"  "15-24 (Age)" "Total (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "9.4"  "8.5"               "7.8"               "8.5"  "8.699999999999999" "7.7"               "6.7"               "6.6"              
"Portugal" "Male (Sex)"  "15-64 (Age)" "Advanced (Aggregate levels)"     "EU Labour Force Survey" "Labour force survey" "3.1"  "2.2"               "3"                 "2.9"  "2.6"               "3.6"               "3.7"               "3.5"              
"Portugal" "Male (Sex)"  "15-64 (Age)" "Basic (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "5.1"  "4.1"               "3.8"               "4"    "4.3"               "4.4"               "4.1"               "4"                
"Portugal" "Male (Sex)"  "15-64 (Age)" "Intermediate (Aggregate levels)" "EU Labour Force Survey" "Labour force survey" "6.3"  "4.6"               "5.5"               "5.7"  "5.1"               "4.8"               "4.4"               "3.7"              
"Portugal" "Male (Sex)"  "15-64 (Age)" "Total (Aggregate levels)"        "EU Labour Force Survey" "Labour force survey" "5.1"  "4"                 "4"                 "4.1"  "4.3"               "4.4"               "4.1"               "3.9"              
end

How do I make the data set long? I would like to generate a Year variable and keep other variables intact.

[Rich Goldstein]

I don't fully understand what you want, or the "roles" of each of your variables; the following works (need to rename the y* variables to get a "stub":

ren y1998Q1 y81
ren y1998Q2 y82
ren y1998Q3 y83
ren y1998Q4 y84
ren y1999Q1 y91
ren y1999Q2 y92
ren y1999Q3 y93
ren y1999Q4 y94
reshape long y, i(Country Sex Age Education) j(yrq)

now, the new "yrq" variable looks like this:

. ta yrq

        yrq |      Freq.     Percent        Cum.
------------+-----------------------------------
         81 |         32       12.50       12.50
         82 |         32       12.50       25.00
         83 |         32       12.50       37.50
         84 |         32       12.50       50.00
         91 |         32       12.50       62.50
         92 |         32       12.50       75.00
         93 |         32       12.50       87.50
         94 |         32       12.50      100.00
------------+-----------------------------------
      Total |        256      100.00

and you can, of course, apply labels to these values if you want; see

help label

[Budu Gulo]

Rich Goldstein : Sorry I could not fully explain what I needed.

I forgot to mention that values are "unemployment rate". So, basically "y" in your code would be the getting the name "unemployment_rate".

The names y* are in year-quarter format; for example y1998Q1 represents quarter 1 of 1998. So, I would like to have a time variable, say "yrq", which would be in time series format and contain values like 1994q1. Note that I am working with 1998-2018 data; rather than manually renaming the variables, how do I rename all the y* variables to get a "stub"? Thank you.

[Budu Gulo]

I have managed to solve the problem by using the solution provided by Clyde here. The following code worked.

reshape long y, i( Country Sex Age Education Survey Type_of_Source ) j(_quarter) string
gen quarter = quarterly(_quarter, "YQ")
format quarter %tq
drop _quarter
rename y unemployment_rate