reshape with many (long-format) variables per id and different years (wide-format)

[Federico Antonioli]

Hi all,

I have the following dataset structure

* Example generated by -dataex-. For more info, type help dataex
clear
input float id str14 country str33 subcategory str31 datatype str60 currentconstant double(sales2010 sales2011 sales2012 sales2013 sales2014 sales2015)
 1 "Austria" "    Convenience Stores"            "Retail Value RSP excl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices"  813.9180897417  740.7515855278   683.817834878  674.1996974405   659.454091683  658.3483477301
 1 "Austria" "    Convenience Stores"            "Retail Value RSP excl Sales Tax" "Historic Current Prices, Forecast Current Prices"                   548.42968       515.53333       487.73929      490.497417      487.473777       491.01959
 2 "Austria" "    Convenience Stores"            "Retail Value RSP incl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices"  934.3779670235  850.3828201859  785.0228744399  773.9812526616  757.0532972521  755.7839031942
 2 "Austria" "    Convenience Stores"            "Retail Value RSP incl Sales Tax" "Historic Current Prices, Forecast Current Prices"                629.59727264    591.83226284    559.92470492   563.091034716   559.619895996    563.69048932
 3 "Austria" "    Convenience Stores"            "Selling space"                   ""                                                                       184.9          163.06         162.042     158.3701068          157.53      156.124896
 4 "Austria" "    Convenience Stores"            "Sites/outlets"                   ""                                                                         845             740             733             711             704             692
 5 "Austria" "    Forecourt Retailers"           "Retail Value RSP excl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices" 1209.2203331167 1089.8617895817 1070.3403649948  1038.520415697 1059.4754658838  1059.254978314
 5 "Austria" "    Forecourt Retailers"           "Retail Value RSP excl Sales Tax" "Historic Current Prices, Forecast Current Prices"                      814.79           758.5          763.43          755.55     783.1727993          790.03
 6 "Austria" "    Forecourt Retailers"           "Retail Value RSP incl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices" 1396.6494847498 1258.7903669668  1236.243121569   1199.49108013 1223.6941630958 1223.4394999527
 6 "Austria" "    Forecourt Retailers"           "Retail Value RSP incl Sales Tax" "Historic Current Prices, Forecast Current Prices"                   941.08245        876.0675       881.76165       872.66025  904.5645831915       912.48465
 7 "Austria" "    Forecourt Retailers"           "Selling space"                   ""                                                                 96.92071983     90.24495192            89.3          86.432           89.14     87.42541207
 8 "Austria" "    Forecourt Retailers"           "Sites/outlets"                   ""                                                                        1831            1686            1634            1541            1591            1536
 9 "Austria" "   Convenience Retailers"          "Retail Value RSP excl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices" 2023.1384228584 1830.6133751094 1754.1581998727 1712.7201131375 1718.9295575668 1717.6033260442
 9 "Austria" "   Convenience Retailers"          "Retail Value RSP excl Sales Tax" "Historic Current Prices, Forecast Current Prices"                  1363.21968      1274.03333      1251.16929     1246.047417    1270.6465763      1281.04959
10 "Austria" "   Convenience Retailers"          "Retail Value RSP incl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices" 2331.0274517733 2109.1731871527 2021.2659960089 1973.4723327917 1980.7474603478 1979.2234031469
10 "Austria" "   Convenience Retailers"          "Retail Value RSP incl Sales Tax" "Historic Current Prices, Forecast Current Prices"               1570.67972264   1467.89976284   1441.68635492  1435.751284716 1464.1844791875   1476.17513932
11 "Austria" "   Convenience Retailers"          "Selling space"                   ""                                                                281.82071983    253.30495192         251.342     244.8021068          246.67    243.55030807
12 "Austria" "   Convenience Retailers"          "Sites/outlets"                   ""                                                                        2676            2426            2367            2252            2295            2228
13 "Austria" "   Discounters"                    "Retail Value RSP excl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices"    7383.9034119 7256.2092725658  7269.490199113 7398.9304888617 7549.9041930581 7746.9358244622
13 "Austria" "   Discounters"                    "Retail Value RSP excl Sales Tax" "Historic Current Prices, Forecast Current Prices"                     4975.38         5050.03         5185.03         5382.91         5580.95         5777.94
14 "Austria" "   Discounters"                    "Retail Value RSP incl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices" 8321.6591452113 8177.7478501816 8192.7154544003 8338.5946609472 8508.7420255764 8730.7966741689
14 "Austria" "   Discounters"                    "Retail Value RSP incl Sales Tax" "Historic Current Prices, Forecast Current Prices"                  5607.25326      5691.38381      5843.52881      6066.53957      6289.73065      6511.73838
15 "Austria" "   Discounters"                    "Selling space"                   ""                                                                  808.120291      812.978601      812.778623      817.837607      829.577756      845.637363
16 "Austria" "   Discounters"                    "Sites/outlets"                   ""                                                                        1239            1244            1227            1211            1203            1195
17 "Austria" "   Food/Drink/Tobacco Specialists" "Retail Value RSP excl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices"   5707.04684495 5461.5797714852 5202.1488774423 4984.8155240003 4773.4598836293 4672.5046631472
17 "Austria" "   Food/Drink/Tobacco Specialists" "Retail Value RSP excl Sales Tax" "Historic Current Prices, Forecast Current Prices"                     3845.49         3801.04         3710.48         3626.58         3528.58         3484.92
18 "Austria" "   Food/Drink/Tobacco Specialists" "Retail Value RSP incl Sales Tax" "Historic Constant 2024 Prices, Forecast Constant 2024 Prices"  6620.174340142 6335.4325349228 6034.4926978331 5782.3860078403   5537.21346501 5420.1054092508
18 "Austria" "   Food/Drink/Tobacco Specialists" "Retail Value RSP incl Sales Tax" "Historic Current Prices, Forecast Current Prices"                   4460.7684       4409.2064       4304.1568       4206.8328       4093.1528       4042.5072
19 "Austria" "   Food/Drink/Tobacco Specialists" "Selling space"                   ""                                                                      366.21          361.27           353.5          345.34          337.28          331.39
20 "Austria" "   Food/Drink/Tobacco Specialists" "Sites/outlets"                   ""                                                                        5393            5348            5252            5121            4987            4872
end

What I would like to have is a panel of country-year, with sales values for each combination of subcategory-datatype-currentconstant (which is how the id has been defined).
Any advice?

Thanks
F

[Hemanshu Kumar]

Does this do what you're looking for?

reshape long sales, i(id country subcategory datatype currentconstant) j(year)
collapse (sum) sales (first) subcategory datatype currentconstant, by(country year id)
drop subcategory datatype currentconstant
reshape wide sales, i(country year) j(id)

[Nick Cox]

In addition to Hemanshu Kumar 's excellent code, I would trim() all string variables. Those extra spaces look like a potential problem if they are not consistent and ugly if they are.