converting panel data to long

[Hamid muili]

dear all, i have a panel data in wide format which i have been struggling to reshape to long format. it has variables on the row and country and year on the columns.

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input str53 indicators double(angola2010 benin2010 botswana2010 burkinafaso2010 burundi2010 capeverde2010 cameroon2010 angola2011 benin2011 botswana2011 burkinafaso2011 burundi2011 capeverde2011 cameroon2011)
"Nominal GDP  (PPP)"                                    1.53887e+11 21750364351 26231500743 20844117539  5762788018  2806636559 54515742872 1.62539e+11 22860401007 29344195184 22680149402  6116117137  2976613884 57992159989
"Real GDP"                                              5.84836e+12  5.3768e+12 6.81701e+10  3.3629e+12   1.508e+12  1.2097e+11 1.12362e+13 7.96905e+12  5.5361e+12 7.22933e+10  3.5857e+12 1.56867e+12 1.27525e+11 1.17001e+13
"Final Consumption  (PPP)"                                        . 19505254678           .           .           .  2286024856           .           . 20358059922           .           .           .  2420039918           .
"Total Tax Revenues  (PPP)"                                       .           .           .  2547904375 771132994.3 521654810.2  6083384570 12788509255  2468281799  3849492708  3028881191 951820911.4 587480209.5  6901207654
"Total Number Of Registered Taxpayers"                            .           .      183575           .        1577           .           .           .           .      222577           .        2080           .           .
"Non-Tax-Revenues  (PPP)"                                         .           .           .           .           .           .  2028837487 37604404198 145927209.7   562843351           . 71409100.17           .  2601012118
"Domestic Tax Revenues  (PPP)"                                    .           .           .  1433354675 390653597.2 252128094.7  3798064712  7921521943  1192024334  2793273191  1657560244 445377664.7 265338114.7  4507168872
"Customs Tax Revenues  (PPP)"                                     .           .           .  1114549701 380479397.2 269526715.6  2285319858  4866987311  1276257465  1056219517  1371320947 506443246.7 322142094.9  2394038783
"Revenue From Agriculture, Forest & Fishing  (PPP)"           .           .           .    447347.6           .           . 162923122.6           .           .           .   546470.41           .           .   289119528
"Revenue From Mining & Quarrying  (PPP)"                      .           .           . 99578149.29   736562.61           . 356790854.2           .           .           . 291023299.4  2008489.96           . 407373762.1
"Revenue From Secondary Sector  (PPP)"                            .           .           . 192058111.1           .           .  2818703176           .           .           . 198838015.6           .           .  3156825447
"Revenue From Tertiary Sector  (PPP)"                             .           .           . 14810637.24           .           .  3032938870           .           .           . 22962765.45           .           .  3348538205
"Domestic VAT Revenues  (PPP)"                                    .           .           . 563153295.3 138584422.7 87924811.93  1260977627 813821570.1 477030315.2 740514417.9 579573168.3 142560537.1 81697601.68  1564809074
"VAT Refunded  (PPP)"                                             .           .           . 36086811.95           .  9843690.07 287979024.2           .           . 466857901.1 103264207.1 11110803.13  7785419.23 299949213.5
"PIT Revenues  (PPP)"                                             .           .           . 303915508.6  97415112.9 94082181.85 712673133.8  1392564233 273711146.5 806360952.6 341683589.2 120619198.5 98137734.82   814680397
"CIT Revenues  (PPP)"                                             .           .           . 314129703.8 119616536.3 65766382.01  1079516630  1821580252 168555447.8  1712618771 548479382.1 150266608.8 76485858.12  1303279880
"Withholding Tax Revenues  (PPP)"                                 .           .           .           .  8854563.75           .           .  1540606083 101392587.8           .           . 16946821.18           .           .
"Presumptive Tax Revenues  (PPP)"                                 .           .           .           . 11710806.72           .           .           .  3985261.72           .           .  1974687.27           .           .
"Other Domestic Tax Revenues  (PPP)"                              .           .           . 196899632.6  3659603.39 14198408.93 392889277.5  2352949806 105287873.1   636951.24 192118661.9  5451225.63 16802339.26 425994910.9
"VAT On Import Revenues  (PPP)"                                   .           .           . 543811402.9 166860789.9 125172849.1  1091890995  1781987506 581263530.1 998247694.1 686424797.3 230795172.7 151804607.7  1167259067
"Import Duty Revenues  (PPP)"                                     .           .           . 406104209.5 68509550.81   109131280  1101945000  1635313301 372538524.1 57971822.92 452440755.4 60925226.68 119487773.1  1126476866
"Excises on Imports Revenues  (PPP)"                              .           .           . 12685916.76 123064719.2 24852279.25 54463401.42           . 17057292.39           . 16648377.06 130115697.2 34329090.75 66445453.56
"Other Customs Tax Revenues  (PPP)"                               .           .           . 151948171.6 22044337.31 10370307.23  37020461.4  1449686505 305398117.9           . 215807017.1 84607150.12 16520623.39 33857395.86
end

------------------ copy up to and including the previous line ------------------



above is the data

[Nick Cox]

See a recent thread https://www.statalist.org/forums/for...to-long-format for discussion.

* Example generated by -dataex-. For more info, type help dataex
clear
input str53 indicators double(angola2010 benin2010 botswana2010 burkinafaso2010 burundi2010 capeverde2010 cameroon2010 angola2011 benin2011 botswana2011 burkinafaso2011 burundi2011 capeverde2011 cameroon2011)
"Nominal GDP  (PPP)"                                    1.53887e+11 21750364351 26231500743 20844117539  5762788018  2806636559 54515742872 1.62539e+11 22860401007 29344195184 22680149402  6116117137  2976613884 57992159989
"Real GDP"                                              5.84836e+12  5.3768e+12 6.81701e+10  3.3629e+12   1.508e+12  1.2097e+11 1.12362e+13 7.96905e+12  5.5361e+12 7.22933e+10  3.5857e+12 1.56867e+12 1.27525e+11 1.17001e+13
"Final Consumption  (PPP)"                                        . 19505254678           .           .           .  2286024856           .           . 20358059922           .           .           .  2420039918           .
"Total Tax Revenues  (PPP)"                                       .           .           .  2547904375 771132994.3 521654810.2  6083384570 12788509255  2468281799  3849492708  3028881191 951820911.4 587480209.5  6901207654
"Total Number Of Registered Taxpayers"                            .           .      183575           .        1577           .           .           .           .      222577           .        2080           .           .
"Non-Tax-Revenues  (PPP)"                                         .           .           .           .           .           .  2028837487 37604404198 145927209.7   562843351           . 71409100.17           .  2601012118
"Domestic Tax Revenues  (PPP)"                                    .           .           .  1433354675 390653597.2 252128094.7  3798064712  7921521943  1192024334  2793273191  1657560244 445377664.7 265338114.7  4507168872
"Customs Tax Revenues  (PPP)"                                     .           .           .  1114549701 380479397.2 269526715.6  2285319858  4866987311  1276257465  1056219517  1371320947 506443246.7 322142094.9  2394038783
"Revenue From Agriculture, Forest & Fishing  (PPP)"           .           .           .    447347.6           .           . 162923122.6           .           .           .   546470.41           .           .   289119528
"Revenue From Mining & Quarrying  (PPP)"                      .           .           . 99578149.29   736562.61           . 356790854.2           .           .           . 291023299.4  2008489.96           . 407373762.1
"Revenue From Secondary Sector  (PPP)"                            .           .           . 192058111.1           .           .  2818703176           .           .           . 198838015.6           .           .  3156825447
"Revenue From Tertiary Sector  (PPP)"                             .           .           . 14810637.24           .           .  3032938870           .           .           . 22962765.45           .           .  3348538205
"Domestic VAT Revenues  (PPP)"                                    .           .           . 563153295.3 138584422.7 87924811.93  1260977627 813821570.1 477030315.2 740514417.9 579573168.3 142560537.1 81697601.68  1564809074
"VAT Refunded  (PPP)"                                             .           .           . 36086811.95           .  9843690.07 287979024.2           .           . 466857901.1 103264207.1 11110803.13  7785419.23 299949213.5
"PIT Revenues  (PPP)"                                             .           .           . 303915508.6  97415112.9 94082181.85 712673133.8  1392564233 273711146.5 806360952.6 341683589.2 120619198.5 98137734.82   814680397
"CIT Revenues  (PPP)"                                             .           .           . 314129703.8 119616536.3 65766382.01  1079516630  1821580252 168555447.8  1712618771 548479382.1 150266608.8 76485858.12  1303279880
"Withholding Tax Revenues  (PPP)"                                 .           .           .           .  8854563.75           .           .  1540606083 101392587.8           .           . 16946821.18           .           .
"Presumptive Tax Revenues  (PPP)"                                 .           .           .           . 11710806.72           .           .           .  3985261.72           .           .  1974687.27           .           .
"Other Domestic Tax Revenues  (PPP)"                              .           .           . 196899632.6  3659603.39 14198408.93 392889277.5  2352949806 105287873.1   636951.24 192118661.9  5451225.63 16802339.26 425994910.9
"VAT On Import Revenues  (PPP)"                                   .           .           . 543811402.9 166860789.9 125172849.1  1091890995  1781987506 581263530.1 998247694.1 686424797.3 230795172.7 151804607.7  1167259067
"Import Duty Revenues  (PPP)"                                     .           .           . 406104209.5 68509550.81   109131280  1101945000  1635313301 372538524.1 57971822.92 452440755.4 60925226.68 119487773.1  1126476866
"Excises on Imports Revenues  (PPP)"                              .           .           . 12685916.76 123064719.2 24852279.25 54463401.42           . 17057292.39           . 16648377.06 130115697.2 34329090.75 66445453.56
"Other Customs Tax Revenues  (PPP)"                               .           .           . 151948171.6 22044337.31 10370307.23  37020461.4  1449686505 305398117.9           . 215807017.1 84607150.12 16520623.39 33857395.86
end

encode indicators, gen(indexes)

su indexes, meanonly 
local max = r(max)

forval j = 1/`max' {
    local label`j' : label (indexes) `j'
}

drop indicators 

ds indexes, not 

rename (`r(varlist)') (Y=)

reshape long Y, i(indexes) j(which) string 

reshape wide Y, i(which) j(indexes)

gen year = real(substr(which, -4, 4))

gen country = substr(which, 1, length(which) -4)

forval j = 1/`max' { 
    label var Y`j' "`label`j''"
}

order country year 

drop which 

describe

Contains data
 Observations:            14                  
    Variables:            25                  
----------------------------------------------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
----------------------------------------------------------------------------------------------------------------------
country         str11   %11s                  
year            float   %9.0g                 
Y1              double  %10.0g                CIT Revenues (PPP)
Y2              double  %10.0g                Customs Tax Revenues (PPP)
Y3              double  %10.0g                Domestic Tax Revenues (PPP)
Y4              double  %10.0g                Domestic VAT Revenues (PPP)
Y5              double  %10.0g                Excises on Imports Revenues (PPP)
Y6              double  %10.0g                Final Consumption (PPP)
Y7              double  %10.0g                Import Duty Revenues (PPP)
Y8              double  %10.0g                Nominal GDP (PPP)
Y9              double  %10.0g                Non-Tax-Revenues (PPP)
Y10             double  %10.0g                Other Customs Tax Revenues (PPP)
Y11             double  %10.0g                Other Domestic Tax Revenues (PPP)
Y12             double  %10.0g                PIT Revenues (PPP)
Y13             double  %10.0g                Presumptive Tax Revenues (PPP)
Y14             double  %10.0g                Real GDP
Y15             double  %10.0g                Revenue From Agriculture, Forest & Fishing (PPP)
Y16             double  %10.0g                Revenue From Mining & Quarrying (PPP)
Y17             double  %10.0g                Revenue From Secondary Sector (PPP)
Y18             double  %10.0g                Revenue From Tertiary Sector (PPP)
Y19             double  %10.0g                Total Number Of Registered Taxpayers
Y20             double  %10.0g                Total Tax Revenues (PPP)
Y21             double  %10.0g                VAT On Import Revenues (PPP)
Y22             double  %10.0g                VAT Refunded (PPP)
Y23             double  %10.0g                Withholding Tax Revenues (PPP)
----------------------------------------------------------------------------------------------------------------------
Sorted by: