Reshape function help needed.

[KADER-YETTEFTI Sami]

Dear all,

I need to merge two datasets for my work, but to do so, I first need to reshape my document such that my variables are the column titles.
I reshaped first using :
reshape long q, i(variables) j(year) string
I tried to separate variables, by(variables),
and use reshape wide variables1 variables2 variables3, i(variables) j(year) string

This is the closest I came to get to what I'm trying to do.
In the other document, I generated a numeric variable called time_var, but the data was in this format. I think what I'm trying to do will help me merge my documents.

Thank you for your time.

[Andrew Musau]

Provide a usable data example, e.g., by copying and pasting the result of

dataex variables year q

[George Ford]

I don't think it will ever work the way it's setup.

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 variables str6 year byte q
"GDP DEFLATOR" "4_1999" 10
"GDP DEFLATOR" "4_2000"  0
"GDP DEFLATOR" "4_2001"  8
"GDP DEFLATOR" "4_2002"  4
"GDP DEFLATOR" "4_2003"  4
"GDP DEFLATOR" "4_2004"  1
"GDP GROWTH"   "4_1999"  3
"GDP GROWTH"   "4_2000"  8
"GDP GROWTH"   "4_2001"  4
"GDP GROWTH"   "4_2002"  2
"GDP GROWTH"   "4_2003"  0
"GDP GROWTH"   "4_2004"  6
end
label var variables "variables" 
label var year "year" 
label var q "q" 

replace year = substr(year,3,.)
destring year, force replace
replace variables = subinstr(variables, " ", "", .)
levelsof variables, local(levels) 
  foreach var of local levels {
     preserve
    keep if variables == "`var'"
    ren q `var'
    drop variables
    g id = 1
    save "`var'", replace
    restore
}

use GDPDEFLATOR, clear
joinby year using GDPGROWTH
reshape wide GDPDEFLATOR GDPGROWTH, i(id) j(year)

[George Ford]

This works too, I think, using the same data. The trick is to create a dataset with the variable names.

ren q GDPDEFLATOR
g GDPGROWTH = GDPDEFLATOR if variables=="GDP GROWTH"
replace GDPDEFLATOR=. if variables=="GDP GROWTH"
collapse (max) GDP* , by(year)
replace year = substr(year,3,.)
destring year, force replace
g id = 1
reshape wide GDPDEFLATOR GDPGROWTH, i(id) j(year)

[Andrew Musau]

Here is an easier way.

*Example generated by -dataex-. For more info, type help dataex
clear
input str12 variables str6 year byte q
"GDP DEFLATOR" "4_1999" 10
"GDP DEFLATOR" "4_2000"  0
"GDP DEFLATOR" "4_2001"  8
"GDP DEFLATOR" "4_2002"  4
"GDP DEFLATOR" "4_2003"  4
"GDP DEFLATOR" "4_2004"  1
"GDP GROWTH"   "4_1999"  3
"GDP GROWTH"   "4_2000"  8
"GDP GROWTH"   "4_2001"  4
"GDP GROWTH"   "4_2002"  2
"GDP GROWTH"   "4_2003"  0
"GDP GROWTH"   "4_2004"  6
end

replace variables= strtoname(variables)
reshape wide q, i(year) j(variables) string
rename q* *

Then convert the year variable to a proper quarterly date variable.

Res.:

. l, sep(0)

     +------------------------------+
     |   year   GDP_DE~R   GDP_GR~H |
     |------------------------------|
  1. | 4_1999         10          3 |
  2. | 4_2000          0          8 |
  3. | 4_2001          8          4 |
  4. | 4_2002          4          2 |
  5. | 4_2003          4          0 |
  6. | 4_2004          1          6 |
     +------------------------------+

[George Ford]

Learn something everyday (often from Andrew).

[KADER-YETTEFTI Sami]

Provide a usable data example, e.g., by copying and pasting the result of

dataex variables year q

* Example generated by -dataex-. To install: ssc install dataex
clear
input str22 variables str6 year double q
"GDP DEFLATOR"        "1_1999" 82.4734
"GDP DEFLATOR"        "1_2000" 83.2103
"GDP DEFLATOR"        "1_2001"  84.854
"GDP DEFLATOR"        "1_2002" 86.6782
"GDP DEFLATOR"        "1_2003" 88.2539
"GDP DEFLATOR"        "1_2004" 89.9728
"GDP DEFLATOR"        "1_2005" 91.4424
"GDP DEFLATOR"        "1_2006"  93.281
"GDP DEFLATOR"        "1_2007" 95.6747
"GDP DEFLATOR"        "1_2008" 98.2669
"GDP DEFLATOR"        "1_2009" 99.1267
"GDP DEFLATOR"        "1_2010"  99.475
"GDP DEFLATOR"        "1_2011" 100.613
"GDP DEFLATOR"        "1_2012" 101.632
"GDP DEFLATOR"        "1_2013" 102.762
"GDP DEFLATOR"        "1_2014" 103.403
"GDP DEFLATOR"        "1_2015" 104.312
"GDP DEFLATOR"        "1_2016" 105.025
"GDP DEFLATOR"        "2_1999" 82.5295
"GDP DEFLATOR"        "2_2000" 83.6444
"GDP DEFLATOR"        "2_2001" 85.3148
"GDP DEFLATOR"        "2_2002" 87.0415
"GDP DEFLATOR"        "2_2003" 88.7135
"GDP DEFLATOR"        "2_2004" 90.1104
"GDP DEFLATOR"        "2_2005" 91.9526
"GDP DEFLATOR"        "2_2006" 93.6899
"GDP DEFLATOR"        "2_2007" 96.1086
"GDP DEFLATOR"        "2_2008" 98.7536
"GDP DEFLATOR"        "2_2009" 98.8111
"GDP DEFLATOR"        "2_2010" 99.8294
"GDP DEFLATOR"        "2_2011"  100.78
"GDP DEFLATOR"        "2_2012" 101.993
"GDP DEFLATOR"        "2_2013" 102.889
"GDP DEFLATOR"        "2_2014"  103.36
"GDP DEFLATOR"        "2_2015" 104.444
"GDP DEFLATOR"        "2_2016" 104.869
"GDP DEFLATOR"        "3_1999" 82.5809
"GDP DEFLATOR"        "3_2000" 84.0526
"GDP DEFLATOR"        "3_2001" 85.7208
"GDP DEFLATOR"        "3_2002" 87.4789
"GDP DEFLATOR"        "3_2003" 89.1515
"GDP DEFLATOR"        "3_2004"   90.43
"GDP DEFLATOR"        "3_2005" 92.2072
"GDP DEFLATOR"        "3_2006" 94.3681
"GDP DEFLATOR"        "3_2007" 96.8226
"GDP DEFLATOR"        "3_2008" 98.9485
"GDP DEFLATOR"        "3_2009" 98.6906
"GDP DEFLATOR"        "3_2010"  100.23
"GDP DEFLATOR"        "3_2011" 101.027
"GDP DEFLATOR"        "3_2012" 102.303
"GDP DEFLATOR"        "3_2013"  102.94
"GDP DEFLATOR"        "3_2014" 103.369
"GDP DEFLATOR"        "3_2015" 104.737
"GDP DEFLATOR"        "3_2016" 105.004
"GDP DEFLATOR"        "4_1998"       .
"GDP DEFLATOR"        "4_1999" 82.6769
"GDP DEFLATOR"        "4_2000" 84.4428
"GDP DEFLATOR"        "4_2001"  86.208
"GDP DEFLATOR"        "4_2002" 87.9549
"GDP DEFLATOR"        "4_2003"  89.533
"GDP DEFLATOR"        "4_2004" 90.9595
"GDP DEFLATOR"        "4_2005" 92.8984
"GDP DEFLATOR"        "4_2006" 95.1662
"GDP DEFLATOR"        "4_2007" 97.5384
"GDP DEFLATOR"        "4_2008" 99.3884
"GDP DEFLATOR"        "4_2009" 99.1082
"GDP DEFLATOR"        "4_2010" 100.462
"GDP DEFLATOR"        "4_2011" 101.354
"GDP DEFLATOR"        "4_2012" 102.523
"GDP DEFLATOR"        "4_2013"  103.03
"GDP DEFLATOR"        "4_2014" 103.656
"GDP DEFLATOR"        "4_2015" 105.004
"GDP DEFLATOR"        "4_2016" 105.247
"GDP growth rate ADV" "1_1999" 3.25195
"GDP growth rate ADV" "1_2000"  4.2098
"GDP growth rate ADV" "1_2001" 2.58175
"GDP growth rate ADV" "1_2002" .852638
"GDP growth rate ADV" "1_2003" 1.57129
"GDP growth rate ADV" "1_2004" 3.48168
"GDP growth rate ADV" "1_2005" 2.48357
"GDP growth rate ADV" "1_2006" 3.29736
"GDP growth rate ADV" "1_2007" 2.40372
"GDP growth rate ADV" "1_2008" 1.74301
"GDP growth rate ADV" "1_2009" -4.8201
"GDP growth rate ADV" "1_2010" 2.09604
"GDP growth rate ADV" "1_2011"  2.3043
"GDP growth rate ADV" "1_2012"  1.6095
"GDP growth rate ADV" "1_2013" .485013
"GDP growth rate ADV" "1_2014" 1.89162
"GDP growth rate ADV" "1_2015" 2.32431
"GDP growth rate ADV" "1_2016"  1.4706
"GDP growth rate ADV" "2_1999" 3.45488
"GDP growth rate ADV" "2_2000" 4.71973
"GDP growth rate ADV" "2_2001" 1.51891
"GDP growth rate ADV" "2_2002" 1.32356
"GDP growth rate ADV" "2_2003" 1.39959
"GDP growth rate ADV" "2_2004" 3.57049
"GDP growth rate ADV" "2_2005" 2.68989
"GDP growth rate ADV" "2_2006" 3.09758
"GDP growth rate ADV" "2_2007" 2.50328
end

[KADER-YETTEFTI Sami]

Here is an easier way.

*Example generated by -dataex-. For more info, type help dataex
clear
input str12 variables str6 year byte q
"GDP DEFLATOR" "4_1999" 10
"GDP DEFLATOR" "4_2000" 0
"GDP DEFLATOR" "4_2001" 8
"GDP DEFLATOR" "4_2002" 4
"GDP DEFLATOR" "4_2003" 4
"GDP DEFLATOR" "4_2004" 1
"GDP GROWTH" "4_1999" 3
"GDP GROWTH" "4_2000" 8
"GDP GROWTH" "4_2001" 4
"GDP GROWTH" "4_2002" 2
"GDP GROWTH" "4_2003" 0
"GDP GROWTH" "4_2004" 6
end

replace variables= strtoname(variables)
reshape wide q, i(year) j(variables) string
rename q* *

Then convert the year variable to a proper quarterly date variable.

Res.:

. l, sep(0)

+------------------------------+
| year GDP_DE~R GDP_GR~H |
|------------------------------|
1. | 4_1999 10 3 |
2. | 4_2000 0 8 |
3. | 4_2001 8 4 |
4. | 4_2002 4 2 |
5. | 4_2003 4 0 |
6. | 4_2004 1 6 |
+------------------------------+

Thank you very much, it worked the way I wanted it!

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(GDP_DEFLATOR GDP_growth_rate_ADV GROSS_DOMESTIC_PRODUCT) str6 year
82.4734  3.25195 3.460e+11 "1_1999"
83.2103   4.2098 3.640e+11 "1_2000"
 84.854  2.58175 3.820e+11 "1_2001"
86.6782  .852638 3.940e+11 "1_2002"
88.2539  1.57129 4.050e+11 "1_2003"
89.9728  3.48168 4.220e+11 "1_2004"
91.4424  2.48357 4.370e+11 "1_2005"
 93.281  3.29736 4.560e+11 "1_2006"
95.6747  2.40372 4.790e+11 "1_2007"
98.2669  1.74301 5.000e+11 "1_2008"
99.1267  -4.8201 4.850e+11 "1_2009"
 99.475  2.09604 4.920e+11 "1_2010"
100.613   2.3043 5.120e+11 "1_2011"
101.632   1.6095 5.190e+11 "1_2012"
102.762  .485013 5.250e+11 "1_2013"
103.403  1.89162 5.330e+11 "1_2014"
104.312  2.32431 5.450e+11 "1_2015"
105.025   1.4706 5.560e+11 "1_2016"
82.5295  3.45488 3.490e+11 "2_1999"
83.6444  4.71973 3.690e+11 "2_2000"
85.3148  1.51891 3.850e+11 "2_2001"
87.0415  1.32356 3.970e+11 "2_2002"
88.7135  1.39959 4.060e+11 "2_2003"
90.1104  3.57049 4.250e+11 "2_2004"
91.9526  2.68989 4.400e+11 "2_2005"
93.6899  3.09758 4.610e+11 "2_2006"
96.1086  2.50328 4.840e+11 "2_2007"
98.7536  1.06098 5.000e+11 "2_2008"
98.8111  -4.6551 4.830e+11 "2_2009"
99.8294  3.01174 4.970e+11 "2_2010"
 100.78  1.55089 5.130e+11 "2_2011"
101.993   1.3731 5.200e+11 "2_2012"
102.889  .912433 5.290e+11 "2_2013"
 103.36  1.80166 5.340e+11 "2_2014"
104.444   2.3334 5.460e+11 "2_2015"
104.869  1.49111 5.550e+11 "2_2016"
82.5809  3.70201 3.530e+11 "3_1999"
84.0526  3.96652 3.740e+11 "3_2000"
85.7208   .94432 3.880e+11 "3_2001"
87.4789  1.99524 4.010e+11 "3_2002"
89.1515  2.15329 4.110e+11 "3_2003"
  90.43  3.01747 4.280e+11 "3_2004"
92.2072  2.89875 4.430e+11 "3_2005"
94.3681  2.53509 4.650e+11 "3_2006"
96.8226  2.75216 4.890e+11 "3_2007"
98.9485  -.13278 5.000e+11 "3_2008"
98.6906 -3.72959 4.830e+11 "3_2009"
 100.23  3.35764 5.020e+11 "3_2010"
101.027  1.36087 5.150e+11 "3_2011"
102.303  .966708 5.230e+11 "3_2012"
 102.94  1.44229 5.300e+11 "3_2013"
103.369  1.93358 5.360e+11 "3_2014"
104.737   1.9904 5.500e+11 "3_2015"
105.004  1.68834 5.560e+11 "3_2016"
      .  2.59527         . "4_1998"
82.6769  4.01827 3.580e+11 "4_1999"
84.4428  3.19661 3.790e+11 "4_2000"
 86.208  .598101 3.900e+11 "4_2001"
87.9549  1.94805 4.030e+11 "4_2002"
 89.533  2.96547 4.160e+11 "4_2003"
90.9595  2.54819 4.330e+11 "4_2004"
92.8984  3.04757 4.500e+11 "4_2005"
95.1662  2.79624 4.720e+11 "4_2006"
97.5384  2.30105 4.950e+11 "4_2007"
99.3884 -2.60893 4.950e+11 "4_2008"
99.1082 -.857645 4.880e+11 "4_2009"
100.462  2.92194 5.060e+11 "4_2010"
101.354  1.16608 5.180e+11 "4_2011"
102.523  .545617 5.240e+11 "4_2012"
 103.03  2.10478 5.320e+11 "4_2013"
103.656  1.89767 5.380e+11 "4_2014"
105.004  1.73272 5.530e+11 "4_2015"
105.247  1.93761 5.600e+11 "4_2016"
end