Assign variables labels based on values of other variables

[Anh Trinh]

Dear Statalist,

I am working on a dataset as below:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id_country str9 indicator_name byte indicator_code int(year1 year2) byte year3 int year4
1 "Name of 1" 1 342  43  3   .
1 "Name of 2" 2  23 526  4  42
1 "Name of 3" 3   2   3 32 235
2 "Name of 1" 1   4  52  5   .
2 "Name of 2" 2   5 865  7   .
2 "Name of 3" 3  34   3  3 532
3 "Name of 1" 1   3   2 54   5
3 "Name of 2" 2 674  32  3  24
3 "Name of 3" 3  52 342  2   3
end

So far I have removed indicator_name in order to reshape from long to wide:

drop indicator_name
reshape wide year*, i(id_country) j(indicator_code)

I would like to use the value in indicator_name as label variable in the new variables. For example, new variable year11 should have a label as "Name of 1 in year 1".
I would appreciate any help.

Thank you!

[Liu Qiang]

If I understand it correctly, you need the code like this:

forvalues year=1/4{
  forvalues name=1/3{
  label var year`year'`name' "Name of `name' in year `year'"
  }
}

[Anh Trinh]

Hi Liu,
Thanks for your response! In my example, I use the same structure for indicator_name to make it simple. In fact, they contain different characters like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id_country str9 indicator_name byte indicator_code int(year1 year2) byte year3 int year4
1 "XYZ" 1 342  43  3   .
1 "ABC" 2  23 526  4  42
1 "DEF" 3   2   3 32 235
2 "XYZ" 1   4  52  5   .
2 "ABC" 2   5 865  7   .
2 "DEF" 3  34   3  3 532
3 "XYZ" 1   3   2 54   5
3 "ABC" 2 674  32  3  24
3 "DEF" 3  52 342  2   3
end

Also, I am not sure how to keep the indicator_name when reshaping. I expect to have a new set of variables with labels taken from indicator_name.

[Andrew Musau]

Also, I am not sure how to keep the indicator_name when reshaping.

As a variable?

reshape wide year* indicator_name, i(id_country) j(indicator_code)

As part of the variable names

drop indicator_code
reshape wide year*, i(id_country) j(indicator_name, string)

[Anh Trinh]

Hi,

This is a small sample of the data that I am working on:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 CountryCode str98 SeriesName str17 indicator float(YR2010 YR2011 YR2012)
"KHM" "Imports of goods and services (% of GDP)"            "Imp"    59.5  59.5  62.7
"KHM" "Exports of goods and services (% of GDP)"            "Expt"   54.1  54.1  57.9
"KHM" "Population density (people per sq. km of land area)" "Popdn"  81.1  82.4  83.7
"THA" "Imports of goods and services (% of GDP)"            "Imp"    60.8  68.8  68.7
"THA" "Exports of goods and services (% of GDP)"            "Expt"   66.5  70.9  69.8
"THA" "Population density (people per sq. km of land area)" "Popdn" 131.6 132.2 132.8
"LAO" "Imports of goods and services (% of GDP)"            "Imp"    49.3  51.4  60.3
"LAO" "Exports of goods and services (% of GDP)"            "Expt"   35.4  40.3  37.9
"LAO" "Population density (people per sq. km of land area)" "Popdn"  27.1  27.4  27.8
end

I would like to reshape the data from long to wide, which means each country will have multiple indicators from 2010 to 2012:

drop SeriesName
reshape wide YR*, i(CountryCode) j(indicator, string)

Now I am looking for a way to label new set of variables using the value from SeriesName (which has been dropped previously). For example,YR2010Expt should have a variable label as "Exports of goods and services (% of GDP) in yr2010".

Thank you!

[Nick Cox]

This is what I would do to get a layout suitable for most Stata purposes.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 CountryCode str98 SeriesName str17 indicator float(YR2010 YR2011 YR2012)
"KHM" "Imports of goods and services (% of GDP)"            "Imp"    59.5  59.5  62.7
"KHM" "Exports of goods and services (% of GDP)"            "Expt"   54.1  54.1  57.9
"KHM" "Population density (people per sq. km of land area)" "Popdn"  81.1  82.4  83.7
"THA" "Imports of goods and services (% of GDP)"            "Imp"    60.8  68.8  68.7
"THA" "Exports of goods and services (% of GDP)"            "Expt"   66.5  70.9  69.8
"THA" "Population density (people per sq. km of land area)" "Popdn" 131.6 132.2 132.8
"LAO" "Imports of goods and services (% of GDP)"            "Imp"    49.3  51.4  60.3
"LAO" "Exports of goods and services (% of GDP)"            "Expt"   35.4  40.3  37.9
"LAO" "Population density (people per sq. km of land area)" "Popdn"  27.1  27.4  27.8
end

gen long obs = _n 
foreach x in Imp Expt Popdn { 
    su obs if indicator == "`x'", meanonly 
    local label`x' = SeriesName[r(min)] 
}

drop obs SeriesName 
reshape long YR, i(CountryCode indicator) j(Year) 
reshape wide YR, i(CountryCode Year) j(indicator) string 

rename YR* * 

foreach v in Imp Expt Popdn { 
    label var `v' "`label`v''" 
} 


. describe

Contains data
  obs:             9                          
 vars:             5                          
 size:           153                          
--------------------------------------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
--------------------------------------------------------------------------------------------------------------------------------------------------
CountryCode     str3    %9s                   
Year            int     %9.0g                 
Expt            float   %9.0g                 Exports of goods and services (% of GDP)
Imp             float   %9.0g                 Imports of goods and services (% of GDP)
Popdn           float   %9.0g                 Population density (people per sq. km of land area)
--------------------------------------------------------------------------------------------------------------------------------------------------
Sorted by: CountryCode  Year
     Note: Dataset has changed since last saved.

. list

     +---------------------------------------+
     | Countr~e   Year   Expt    Imp   Popdn |
     |---------------------------------------|
  1. |      KHM   2010   54.1   59.5    81.1 |
  2. |      KHM   2011   54.1   59.5    82.4 |
  3. |      KHM   2012   57.9   62.7    83.7 |
  4. |      LAO   2010   35.4   49.3    27.1 |
  5. |      LAO   2011   40.3   51.4    27.4 |
     |---------------------------------------|
  6. |      LAO   2012   37.9   60.3    27.8 |
  7. |      THA   2010   66.5   60.8   131.6 |
  8. |      THA   2011   70.9   68.8   132.2 |
  9. |      THA   2012   69.8   68.7   132.8 |
     +---------------------------------------+

[Anh Trinh]

Hi Nick,
Thanks so much for your help!
Anh