Convert observations into variables

[Ana Vasconcelos]

I have a dataset with a variable named "ind". The datset was construted in a way that the observations of this variable (e.g. FAM18A, FAM18B, FAM18C, etc..) are the variables. I would like to out each of the variable in a column.
Can you help me with this?
I send an example of the dataset below.
Thank you in advance.

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 cou str28 country str6(sex v4 ind) strL indicator int(year v8) str15 unitcode str28 unit byte powercodecode str5 powercode byte(referenceperiodcode referenceperiod) float value byte(flagcodes flags)
"AUS" "Australia" "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2000 2000 "NBR" "Number" 0 "Units" . . 512.6 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2003 2003 "NBR" "Number" 0 "Units" . . 506.1 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 494.9 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 496.1 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 495.1 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 487.2 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 487.2 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2000 2000 "NBR" "Number" 0 "Units" . . 546.3 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2003 2003 "NBR" "Number" 0 "Units" . . 545.4 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 531.8 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 532.9 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 529.5 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 518.9 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 518.6 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2003 2003 "NBR" "Number" 0 "Units" . . 526.9 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2006 2006 "NBR" "Number" 0 "Units" . .   527 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2009 2009 "NBR" "Number" 0 "Units" . . 519.4 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2012 2012 "NBR" "Number" 0 "Units" . . 510.1 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2015 2015 "NBR" "Number" 0 "Units" . . 496.8 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2018 2018 "NBR" "Number" 0 "Units" . . 494.3 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2003 2003 "NBR" "Number" 0 "Units" . . 521.6 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2006 2006 "NBR" "Number" 0 "Units" . . 512.5 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2009 2009 "NBR" "Number" 0 "Units" . . 509.5 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2012 2012 "NBR" "Number" 0 "Units" . . 497.8 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2015 2015 "NBR" "Number" 0 "Units" . .   491 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2018 2018 "NBR" "Number" 0 "Units" . . 488.3 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 526.9 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 526.8 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 523.7 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2015 2015 "NBR" "Number" 0 "Units" . .   511 . .
"AUS" "Australia" "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 503.7 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 526.9 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 527.7 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 519.1 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 508.9 . .
"AUS" "Australia" "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 502.2 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2000 2000 "NBR" "Number" 0 "Units" . . 475.8 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2003 2003 "NBR" "Number" 0 "Units" . . 467.1 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 468.3 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 449.3 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 471.1 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 474.8 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 470.6 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2000 2000 "NBR" "Number" 0 "Units" . . 509.2 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2003 2003 "NBR" "Number" 0 "Units" . . 514.4 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 512.9 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 490.5 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2012 2012 "NBR" "Number" 0 "Units" . .   508 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 495.1 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 498.7 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2003 2003 "NBR" "Number" 0 "Units" . . 509.4 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2006 2006 "NBR" "Number" 0 "Units" . . 516.6 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2009 2009 "NBR" "Number" 0 "Units" . . 505.7 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2012 2012 "NBR" "Number" 0 "Units" . . 516.7 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2015 2015 "NBR" "Number" 0 "Units" . . 510.1 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2018 2018 "NBR" "Number" 0 "Units" . . 505.5 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2003 2003 "NBR" "Number" 0 "Units" . . 501.8 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2006 2006 "NBR" "Number" 0 "Units" . .   494 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2009 2009 "NBR" "Number" 0 "Units" . . 486.5 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2012 2012 "NBR" "Number" 0 "Units" . . 494.5 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2015 2015 "NBR" "Number" 0 "Units" . . 483.1 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2018 2018 "NBR" "Number" 0 "Units" . . 492.2 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 514.5 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 498.3 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 510.1 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 504.4 . .
"AUT" "Austria"   "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 490.9 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2006 2006 "NBR" "Number" 0 "Units" . .   507 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 490.5 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 501.5 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 485.5 . .
"AUT" "Austria"   "FEMALE" "Female" "FAM18C" "Country mean average score in science, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 488.6 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2000 2000 "NBR" "Number" 0 "Units" . . 492.4 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2003 2003 "NBR" "Number" 0 "Units" . . 489.3 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2006 2006 "NBR" "Number" 0 "Units" . .   482 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 492.7 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 492.8 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 490.7 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18A" "Country mean average score in reading, by sex"     2018 2018 "NBR" "Number" 0 "Units" . .   482 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2000 2000 "NBR" "Number" 0 "Units" . . 525.2 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2003 2003 "NBR" "Number" 0 "Units" . . 526.2 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 521.7 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 519.8 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2012 2012 "NBR" "Number" 0 "Units" . . 524.6 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2015 2015 "NBR" "Number" 0 "Units" . . 506.6 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18A" "Country mean average score in reading, by sex"     2018 2018 "NBR" "Number" 0 "Units" . . 503.7 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2003 2003 "NBR" "Number" 0 "Units" . . 532.9 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2006 2006 "NBR" "Number" 0 "Units" . . 523.7 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2009 2009 "NBR" "Number" 0 "Units" . . 525.9 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2012 2012 "NBR" "Number" 0 "Units" . . 520.1 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2015 2015 "NBR" "Number" 0 "Units" . .   514 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18B" "Country mean average score in mathematics, by sex" 2018 2018 "NBR" "Number" 0 "Units" . . 514.1 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2003 2003 "NBR" "Number" 0 "Units" . . 525.4 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2006 2006 "NBR" "Number" 0 "Units" . . 516.7 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2009 2009 "NBR" "Number" 0 "Units" . . 504.2 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2012 2012 "NBR" "Number" 0 "Units" . . 508.9 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2015 2015 "NBR" "Number" 0 "Units" . . 499.7 . .
"BEL" "Belgium"   "FEMALE" "Female" "FAM18B" "Country mean average score in mathematics, by sex" 2018 2018 "NBR" "Number" 0 "Units" . .   502 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2006 2006 "NBR" "Number" 0 "Units" . . 510.7 . .
"BEL" "Belgium"   "MALE"   "Male"   "FAM18C" "Country mean average score in science, by sex"     2009 2009 "NBR" "Number" 0 "Units" . . 509.7 . .
end

[Clyde Schechter]

It appears that your observations are uniquely identified by the combination of ind, country, year, and sex, and that the values of the variable "indicator" are in 1:1 correspondence with those of ind. If this is correct in your full data:

replace ind = lower(ind)
drop indicator

reshape wide value, i(country year sex) j(ind) string
rename value* *

That said, for most purposes, you would be better off leaving the data as you found them. Going to this wide layout of the values is only useful for a small number of purposes. Unless you know that you will be doing one of those things, I would advise not doing this as it will complicate whatever else you plan to do.

[Ana Vasconcelos]

Thank you very much Clyde Schechter , I just don´t know how can I work with the data if I leave it as I found it, How can I make a regression with variables that are observations of "ind"?
For example reg fam18C fam18B..How can I regress it if stata thinks these variables are observations?
Thank you in advance.

[Clyde Schechter]

If fam18C, fam18B and fam18A are going to be predictors in a regression, then they must be separate variables in wide layout. So go right ahead. It wasn't at all clear to me that you were going to use them in that way.

[Ana Vasconcelos]

Thank you very much Clyde Schechter