Separating 2 types of Observations into 2 different variables

[Jonathan Lau]

Dear Statalisters,

My dataset consists of Year-on-Year Property Price Growth rate & Real Property Price Indices (with Index=100 for Year 2010) of 61 countries/regions. An example of the dataset is as follows,where the variables mean:

REF_AREA	An Area/Country's Abbreviation/Code
REF_AREAName	Country/Region Name
UNIT_MEASURE	A 3-digit number that corresponds to Measurement,with 628 denoting Real Property Price Indices (Index=100 in Yr 2010) and 771 denoting Year-on-Year Property Price Growth Rate
UNIT_MEASUREName	Name of the measurement, whether that observation is talking about Real Property Price Indices or Year-on-Year Property Price Growth Rate
Time	Self-explanatory; Date in format of Year & Quarter
PropertyPriceVar	Value of Real Property Price Index or Property Price Growth Rate of a country/region in a particular year & quarter

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 REF_AREA str25 REF_AREAName str3 UNIT_MEASURE str33 UNIT_MEASUREName int Time double PropertyPriceVar
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 191   96.1
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 192   95.6
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 193  95.92
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 194  96.07
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 195  95.59
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 196   94.5
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 197  94.92
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 198  96.17
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 199  97.14
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 200  98.44
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 201 100.16
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 202 101.01
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 203 100.39
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 204  98.56
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 205 100.03
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 206 100.27
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 207 100.61
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 208  101.3
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 209 102.41
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 210 102.81
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 211 103.96
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 212 104.45
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 213 105.96
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 214 106.91
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 215 107.24
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 216 107.99
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 217  108.9
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 218 108.04
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 219  107.6
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 220 107.23
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 221 107.09
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 222 106.96
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 223 106.73
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 224 106.08
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 225 107.94
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 226 109.21
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 227 110.43
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 228 110.11
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 229 111.92
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 230 111.94
"4T" "Emerging market economies" "628" "Index, 2010 = 100"                 231 111.94
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 195   -.53
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 196  -1.15
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 197  -1.04
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 198     .1
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 199   1.62
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 200   4.17
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 201   5.52
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 202   5.04
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 203   3.34
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 204    .12
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 205   -.13
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 206   -.73
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 207    .22
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 208   2.78
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 209   2.38
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 210   2.54
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 211   3.33
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 212    3.1
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 213   3.47
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 214   3.98
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 215   3.15
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 216   3.39
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 217   2.77
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 218   1.06
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 219    .34
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 220   -.71
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 221  -1.66
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 222   -.99
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 223   -.81
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 224  -1.07
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 225    .79
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 226    2.1
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 227   3.46
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 228    3.8
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 229   3.68
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 230    2.5
"4T" "Emerging market economies" "771" "Year-on-year changes, in per cent" 231   1.37
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 191 114.59
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 192 112.06
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 193 108.14
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 194 104.78
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 195 102.65
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 196 100.87
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 197  99.76
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 198 100.46
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 199 100.63
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 200 100.71
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 201 100.32
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 202  99.96
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 203  99.01
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 204  97.85
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 205  96.58
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 206  96.92
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 207  96.02
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 208  95.94
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 209  96.09
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 210  96.81
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 211  97.03
"5R" "Advanced economies"        "628" "Index, 2010 = 100"                 212  97.63
end
format %tq Time

Since the observations in the dataset have both Real Property Price Index & Growth Rate(denoted by UNIT_MEASURE or UNIT_MEASUREName), I want to separate the observations into two different variables,one for Real Property Price Index & another for Property Price Growth Rate. That is, something that looks like the following:

REF_AREA	REF_AREAName			Time	Real Property Price Index	Property Price Growth Rate
4T	Emerging market economies			2007q4	96.1	.
4T	Emerging market economies			2008q1	95.6	.
...	...			...	...	...
4T	Emerging market economies			2017q4	111.94	1.37

Does anybody have any suggestions on how to do this?

I've tried creating 2 separate datasets from this original dataset,one for Price Index & another for Price Growth Rate,and then attempted to merge them. But it failed. It'll be great if someone has a better solution to this problem. Thank you very much.

[Clyde Schechter]

Well, what you ask is not exactly possible, but you can come close. The difficulty is that in Stata, variable names may not contain embedded blanks. But subject to that constraint, there is this:

rename PropertyPriceVar _
drop UNIT_MEASUREName
reshape wide _, i(REF_AREA Time) j(UNIT_MEASURE) string
rename _628 real_property_price_index
rename _771 property_price_growth_rate

Thank you for using -dataex- on your very first post!

[Jonathan Lau]

Well, what you ask is not exactly possible, but you can come close. The difficulty is that in Stata, variable names may not contain embedded blanks. But subject to that constraint, there is this:

rename PropertyPriceVar _
drop UNIT_MEASUREName
reshape wide _, i(REF_AREA Time) j(UNIT_MEASURE) string
rename _628 real_property_price_index
rename _771 property_price_growth_rate

Thank you for using -dataex- on your very first post!

Thank you very much for your help,Clyde. It's very helpful. I wrongly thought of "reshape long".Maybe I should read the help file for reshape command again to familiarize myself with it.