double reshape

[Tim Evans]

Hi,

I'm using Stata 14.1 and trying to doubly reshape my data so that it appears stacked. I can get the first 'long' reshape, but I'm struggling to get the second part. This is where I have got to:

clear
input str7 AreaName str4 Period long(M_0004 M_0509 M_1014 M_1519 M_2024 M_2529 M_3034 M_3539 M_4044 M_4549 M_5054 M_5559 M_6064 M_6569 M_7074 M_7579 M_8084 M_8589 M_90plus F_0004 F_0509 F_1014 F_1519 F_2024 F_2529 F_3034 F_3539 F_4044 F_4549)
"England" "2004" 1474737 1566890 1659304 1638039 1612443 1574507 1812501 1962909 1869369 1634361 1521405 1604203 1231426 1076523  895635 692564 475113 187626  83979 1401253 1492976 1579930 1580533 1594161 1578822 1823118 1985399 1898815 1651672
"England" "2005" 1489377 1551792 1648647 1644949 1654536 1617344 1777755 1950992 1917462 1677804 1514230 1617936 1271742 1083841  900571 701484 472925 205721  87535 1418610 1478933 1568366 1603340 1639191 1630918 1790464 1964004 1946375 1693471
"England" "2006" 1517066 1527947 1629424 1675418 1678832 1666271 1728120 1936165 1942847 1727711 1520120 1617939 1328105 1073898  906698 712368 474101 225744  88916 1446049 1459156 1552551 1615611 1676515 1678254 1742751 1953220 1974982 1740167
"England" "2007" 1561764 1502203 1622200 1698863 1715740 1722578 1681679 1917391 1961660 1767780 1541787 1539914 1436065 1076935  918885 723039 478984 242918  90148 1487256 1435294 1545268 1633703 1703376 1736540 1687942 1934900 1996365 1790237
"England" "2008" 1608314 1490445 1613019 1700494 1731133 1782203 1666283 1888467 1964029 1810554 1576534 1496115 1500831 1102407  939066 731716 486381 258463  88472 1534391 1421609 1537723 1636675 1734272 1781843 1668622 1906065 1991443 1839966
"England" "2009" 1642702 1490443 1603025 1710701 1726548 1792643 1689757 1843759 1961110 1857065 1612528 1480293 1528799 1142102  958402 738399 496067 264510  94979 1569203 1421329 1525814 1644314 1739519 1803055 1678415 1866507 1986906 1889791
"England" "2010" 1679066 1500427 1593131 1709207 1763534 1802782 1721739 1805612 1936678 1901351 1653809 1474029 1547593 1185186  969405 749553 509886 270747 103509 1601428 1433924 1516108 1633924 1752004 1820822 1717334 1826256 1967761 1935487
"England" "2011" 1703718 1530499 1570266 1701061 1815548 1825924 1765282 1755657 1921918 1926100 1698988 1484784 1551998 1242613  963578 761127 524192 278106 111803 1625028 1459636 1497145 1623209 1780713 1831303 1763868 1766715 1959565 1965976
"England" "2012" 1736916 1578490 1539837 1685620 1833395 1825589 1798016 1707213 1901368 1939398 1748433 1509855 1476180 1358608  972550 777026 538259 285447 121248 1656440 1505092 1468034 1600686 1788156 1833988 1809201 1716140 1941348 1982210
"England" "2013" 1748820 1631984 1523725 1670664 1829362 1840624 1831757 1681435 1877592 1939699 1792829 1539583 1440004 1419680  998923 798929 549133 291910 127316 1665310 1555935 1452668 1584088 1774376 1844708 1850268 1687739 1912301 1986075
"England" "2014" 1757740 1675149 1521497 1658765 1837360 1861199 1844311 1694773 1838430 1935219 1839842 1576145 1426088 1446590 1040581 820363 560580 302999 135565 1673217 1597216 1451558 1572189 1769057 1857183 1862898 1701231 1868974 1983144
"England" "2015" 1760388 1718905 1535237 1650514 1832468 1887380 1857883 1730642 1805140 1921807 1883378 1621661 1422682 1464784 1081238 830890 571581 312363 140345 1674292 1638558 1465058 1562775 1759789 1870588 1870145 1740276 1831314 1967553
"England" "2016" 1757639 1755683 1572421 1631799 1824091 1924216 1874897 1773846 1756427 1919824 1911583 1670419 1436605 1470578 1137445 827832 584219 324015 147381 1671407 1672583 1497833 1547611 1735865 1887371 1874726 1783165 1778839 1963255
end

reshape long M F,i(AreaName Period) j(AgeBand) string
egen T = rowtotal(M - F)

What I want to do is reshape the data long again so that I have one column (called Sex) that contains M, F and T and a new column that contains the value for each of M, F and T
Like this


AreaName Period AgeBand Sex population
England 2004 _0004 M 1474737
England 2004 _0509 M 1566890
England 2004 _1014 M 1659304
England 2004 _1519 M 1638039
England 2004 _0004 F 1401253
England 2004 _0509 F 1492976
England 2004 _1014 F 1579930
England 2004 _1519 F 1580533


Any help appreciated

Thanks
Tim

[Tim Evans]

I've managed to solve this now, I've added the following to the code above:

foreach var of varlist M F T {
  rename `var' population`var'
   }

reshape long population, i(AreaName AgeBand Period) j(sex) string