Setting Time in panel data set

[Nikolas Plchr]

Hello this is my dataset and I want to set time, but it seems to be getting all quarters1 until the end and then all quarters 2 and goes on. The command im using is: encode time, generate(time_numeric) and encode CompanyCode , generate (CompCode) as I want company code to stay the same and after getting all of its values to change company code and then xtset CompCode time_numeric input str6 CompanyCode str4 SICCode float(mtbvalue marketvalue price return beta) str6 time
"13258P" "912" . 10352.64 7 157.55 . "Q12004"
"13259U" "6512" .12 4.73 74.435 5.04 . "Q12004"
"13407C" "6029" . 3052.43 4.38 180.72 . "Q12004"
"13410M" "3569" 1.82 44.8 8.55 72.42 1.25 "Q12004"
"13454U" "6231" 1.95 4969.64 22.225 136.1 .44 "Q12004"
"134982" "2841" 2.67 245.14 108.13 115.11 1.23 "Q12004"
"135109" "7371" .98 6.88 67.38 10.85 .79 "Q12004"
"135127" "3841" 2.16 14.92 10.5 11.69 1.14 "Q12004"
"135229" "4011" 3.26 1150.19 230.53 277.72 1.88 "Q12004"
"135264" "3944" 5.59 65.88 146.5 285.83 .95 "Q12004"
"135540" "8711" 1.45 139.25 214 233.39 1.79 "Q12004"
"135588" "5172" 2.07 648.46 784.58 534.62 .68 "Q12004"
"135590" "6512" .55 237.08 27.05 311.14 1.42 "Q12004"
"135592" "6282" 2.45 789.35 452.43 818.81 2.8 "Q12004"
"135750" "7375" 2.59 182.9 252.96 1284.57 .81 "Q12004"
"135861" "2834" 4.72 98.67 371.5 708.42 .71 "Q12004"
"13635T" "3812" 2.31 99.27 6.65 63.33 .52 "Q12004"
"13653C" "3541" .82 68.26 4.616 5.1 .68 "Q12004"
"136684" "6282" 4.32 4815.35 256.54 1234.66 1.24 "Q12004"
"136700" "6029" 1.52 3503.34 1246.939 758.08 .73 "Q12004"
"136751" "5092" 6.42 218.93 717.5 829.79 1.2 "Q12004"
"136985" "3269" .7 19.11 178.5 88.99 .77 "Q12004"
"13703L" "3511" 14.42 35.91 6.034 7.67 1.98 "Q12004"
"137538" "7941" -3.81 22.79 66.99 113.74 .24 "Q12004"
"13807C" "4833" .01 53.73 199 119.05 . "Q12004"
"138535" "7371" 3.12 126.48 143.95 483.51 2.74 "Q12004"
"13863H" "5651" 4.69 9873.54 3.168 108.87 1.19 "Q12004"
"13922L" "4581" 1.11 2259.05 25 72.64 1.79 "Q12004"
"139275" "3086" 1.04 29.91 82.5 95.73 .19 "Q12004"
"142126" "6029" 2.09 92782.38 488.2 85.19 . "Q12004"
"142150" "3845" 2.89 3929.05 10.96 131.72 1.04 "Q12004"
"142179" "2026" 2.1 234.46 2 30543.68 1 "Q12004"
"142190" "1623" 2.07 3875 .65 298.04 .76 "Q12004"
"142196" "6331" .6 157.6 .31 156701.3 1.03 "Q12004"
"142314" "7011" 1.19 553.85 18.53 1603.48 .48 "Q12004"
"142344" "2657" 1.67 1194 49.75 229.58 .41 "Q12004"
"142358" "1611" 1.06 511.41 3.38 376.99 .84 "Q12004"
"142388" "4581" 2.07 6688.5 735 287.41 .2 "Q12004"
"142412" "6029" 1.22 1444.78 24.14 377.83 .75 "Q12004"
"142413" "6029" 1.24 1253.43 24.91 317.31 .84 "Q12004"
"142414" "6029" 1.35 2404.62 29.25 607.74 1.03 "Q12004"
"142423" "4412" 5.02 3996 33.3 559.07 1.45 "Q12004"
"142428" "2013" .84 350.4 76.567 998.05 .59 "Q12004"
"142439" "3559" .56 23.96 1.28 31.77 .17 "Q12004"
"142440" "4812" 2.34 15942.37 3.8802 117.32 .28 "Q12004"
"142572" "2844" 3.33 273.42 4.3964 693.71 .97 "Q12004"
"142586" "3694" -.24 6.78 1.777 5.23 .53 "Q12004"
"142602" "2841" 2.01 142.43 1.88 249.47 .44 "Q12004"
"142666" "2499" -3.91 37.93 1.8506 33.51 -.08 "Q12004"
"142677" "3714" 1.06 59.63 2.23 55.14 1.26 "Q12004"
"142805" "2053" -.39 13.38 .13 1786.5 1.09 "Q12004"
"142836" "1531" 1.88 857.71 5.4272 559.32 1.08 "Q12004"
"142865" "7999" 1.62 1524.54 20.75 625.92 .82 "Q12004"
"142903" "6512" 1.69 1384.44 25.51 591.42 1.11 "Q12004"
"142963" "2819" 1.43 138.61 9.3592 238.72 .81 "Q12004"
"143051" "6029" 1.59 1726.45 5.4633 347.55 1.51 "Q12004"
"143170" "5995" 2.28 761.25 9.063 182.76 .23 "Q12004"
"143191" "1021" 1.02 1135.5 44.4 112.94 1.59 "Q12004"
"143198" "4833" 8.11 3653.32 27.7 753.39 .99 "Q12004"
"143236" "6311" .43 154.17 16.31 182.21 .58 "Q12004"
"143238" "3842" . 54.62 1.3146 205.81 .57 "Q12004"
"143267" "2879" 1.28 1188.12 5.125 211.92 1.12 "Q12004"
"143328" "7699" 3.15 31206.77 30 1702.38 .9 "Q12004"
"143366" "3711" .99 15443.58 54.2 242.29 1.86 "Q12004"
"143369" "7999" 1.31 394.02 20.7323 367.54 1.53 "Q12004"
"143373" "6311" 1.34 3207.88 26.6 257.16 .44 "Q12004"
"143375" "3674" 3.11 20850.03 23.14 760.6 1.33 "Q12004"
"143378" "6331" 2.26 726.75 .3 19273.12 1.23 "Q12004"
"143406" "3845" 5.38 1879.44 28.75 563.28 1.41 "Q12004"
"143409" "4953" 15.57 217.55 9.2628 282.13 1.79 "Q12004"
"143451" "6512" .91 1012.36 111.6091 191.64 .91 "Q12004"
"143483" "6029" 1.63 410 64.48 322.52 .87 "Q12004"
"143495" "2834" 2.16 450794.4 2420 1615.64 .63 "Q12004"
"14360D" "7372" 3.14 50.2 5 98.04 1.53 "Q12004"
"143641" "3624" 1.09 307.48 8.76 50.47 1.18 "Q12004"
"143687" "6029" 1.81 783999.9 2800 2819.15 1.38 "Q12004"
"143689" "3589" 2.09 191.38 4.39 3.6 1.16 "Q12004"
"143693" "7372" 2.98 426.43 39.0106 535.8 1.14 "Q12004"
"143721" "3842" 21.38 13918.2 39.6 964.86 1.27 "Q12004"
"143730" "3011" 2.44 651.6 6.1 1221.61 1.43 "Q12004"
"143784" "6029" 1.68 73365.38 114.98 473.16 .59 "Q12004"
"143980" "3714" 2.3 425.33 1.218 201.29 1.07 "Q12004"
"143981" "2851" 1.4 3076.47 14.159 110.6 .71 "Q12004"
"14486T" "2911" . 5584.7 .1357 221.83 . "Q12004"
"14560V" "2819" . 22.65 3 107.2 . "Q12004"
"146125" "6726" .82 71.94 89.25 89.25 1.14 "Q12004"
"14653D" "1021" 1.55 441501.4 1852.57 563.92 . "Q12004"
"14822X" "4922" 1.15 6666.55 1.8092 132.11 .59 "Q12004"
"14851R" "3339" .39 3492.53 26.89 18.9 .74 "Q12004"
"14861Q" "4911" 1.09 4584.68 9.16 165.25 1.42 "Q12004"
"14866R" "6029" 1.16 28674.72 17.6671 128.17 1.5 "Q12004"
"14888U" "7941" 2.67 215.86 .4814 48.54 .38 "Q12004"
"15145R" "7372" 2.82 76.95 1.42 49.7 .71 "Q12004"
"152001" "3559" 6.06 8428.62 18.5029 1247.83 1.74 "Q12004"
"152003" "3553" 1.27 22.33 7.6214 104.63 .72 "Q12004"
"15205Q" "6726" .74 28.25 56.5 56.5 .92 "Q12004"
"152141" "3711" .86 93.6 .78 4554.74 1.11 "Q12004"
"152311" "4812" 3.92 10611.25 7.0695 351.03 .66 "Q12004"
"152318" "1542" 1.88 936.95 36.7 390.18 .61 "Q12004"
"152322" "4491" .8 60 .77 223.17 1.38 "Q12004"
end
[/CODE]

And then it gives me that with the commands i said earlier input str6 CompanyCode str4 SICCode float(mtbvalue marketvalue price return beta) str6 time long(time_numeric CompCode)
"13258P" "912" . 10352.64 7 157.55 . "Q12004" 1 1
"13258P" "912" . 10944.23 7.4 169.43 . "Q12005" 2 1
"13258P" "912" . 15700.74 9.2 210.64 . "Q12006" 3 1
"13258P" "912" . 20393.9 11.95 273.61 . "Q12007" 4 1
"13258P" "912" . 19113.95 11.2 256.43 . "Q12008" 5 1
"13258P" "912" .72 17066.03 10 228.96 . "Q12009" 6 1
"13258P" "912" .41 8833.52 5.2 119.06 . "Q12010" 7 1
"13258P" "912" .59 16121.83 9.5 220.12 . "Q12011" 8 1
"13258P" "912" .76 21395.21 12.6 298.44 . "Q12012" 9 1
"13258P" "912" .83 26319.51 15.5 377.02 . "Q12013" 10 1
"13258P" "912" 1.11 37356.72 22 569.64 . "Q12014" 11 1
"13258P" "912" 1.9 65782.44 36.1 979.96 . "Q12015" 12 1
"13258P" "912" 2.52 74984.69 41.15 1162.21 . "Q12016" 13 1
"13258P" "912" 1.66 53026.86 29.1 859.44 . "Q12017" 14 1
"13258P" "912" 1.69 62957.98 34.55 1053.16 . "Q12018" 15 1
"13258P" "912" 1.39 59677.98 32.75 1018.6 . "Q12019" 16 1
"13258P" "912" 1.46 76674.38 39.2 1258.6 . "Q12020" 17 1
"13258P" "912" 1.78 104449.3 53.4 1760.07 . "Q12021" 18 1
"13258P" "912" 2.16 147676.5 75.5 2547.97 . "Q12022" 19 1
"13258P" "912" 2.64 180928.2 92.5 3196.6 . "Q12023" 20 1
"13258P" "912" . 10278.7 6.95 159.13 . "Q22004" 21 1
"13258P" "912" . 13652.82 8 183.17 . "Q22005" 22 1
"13258P" "912" . 20393.9 11.95 273.61 . "Q22006" 23 1
"13258P" "912" . 21332.53 12.5 286.2 . "Q22007" 24 1
"13258P" "912" . 19113.95 11.2 256.43 . "Q22008" 25 1
"13258P" "912" .62 14506.12 8.5 194.62 . "Q22009" 26 1
"13258P" "912" .65 13929.79 8.2 187.75 . "Q22010" 27 1
"13258P" "912" .59 16121.83 9.5 220.12 . "Q22011" 28 1
"13258P" "912" .9 25215.79 14.85 351.73 . "Q22012" 29 1
"13258P" "912" .82 26149.7 15.4 374.58 . "Q22013" 30 1
"13258P" "912" 1.67 56489.08 31 841.52 . "Q22014" 31 1
"13258P" "912" 2.01 69700.19 38.25 1038.32 . "Q22015" 32 1
"13258P" "912" 2.23 66556.88 36.52 1078.73 . "Q22016" 33 1
"13258P" "912" 1.79 57035.73 31.3 924.41 . "Q22017" 34 1
"13258P" "912" 1.46 54393.53 29.85 909.89 . "Q22018" 35 1
"13258P" "912" 1.28 55031.27 30.2 969.64 . "Q22019" 36 1
"13258P" "912" 1.43 75305.19 38.5 1268.96 . "Q22020" 37 1
"13258P" "912" 1.69 99363.75 50.8 1714.4 . "Q22021" 38 1
"13258P" "912" 2.86 195598.1 100 3455.79 . "Q22022" 39 1
"13258P" "912" 2.46 168605.5 86.2 3083.81 . "Q22023" 40 1
"13258P" "912" . 9391.33 6.35 145.39 . "Q32004" 41 1
"13258P" "912" . 14250.13 8.35 191.18 . "Q32005" 42 1
"13258P" "912" . 20820.55 12.2 279.33 . "Q32006" 43 1
"13258P" "912" . 20479.23 12 274.75 . "Q32007" 44 1
"13258P" "912" . 17066.03 10 228.96 . "Q32008" 45 1
"13258P" "912" .62 14506.12 8.5 194.62 . "Q32009" 46 1
"13258P" "912" .79 16970.34 10 231.71 . "Q32010" 47 1
"13258P" "912" .62 16980.33 10 236.86 . "Q32011" 48 1
"13258P" "912" .82 22923.47 13.5 328.37 . "Q32012" 49 1
"13258P" "912" .86 27168.53 16 414.29 . "Q32013" 50 1
"13258P" "912" 1.55 52206.84 28.65 777.72 . "Q32014" 51 1
"13258P" "912" 2.19 75986.88 41.7 1177.74 . "Q32015" 52 1
"13258P" "912" 1.77 52844.63 29 856.48 . "Q32016" 53 1
"13258P" "912" 1.87 59769.07 32.8 999.81 . "Q32017" 54 1
"13258P" "912" 1.65 61591.3 33.8 1051.26 . "Q32018" 55 1
"13258P" "912" 1.42 60953.52 33.45 1073.99 . "Q32019" 56 1
"13258P" "912" 1.52 80195.19 41 1351.36 . "Q32020" 57 1
"13258P" "912" 1.94 114033.6 58.3 1967.5 . "Q32021" 58 1
"13258P" "912" 2.57 176038.3 90 3110.21 . "Q32022" 59 1
"13258P" "912" 2.3 157652 80.6 2883.47 . "Q32023" 60 1
"13258P" "912" . 11240.02 7.6 174.01 . "Q42004" 61 1
"13258P" "912" . 15956.73 9.35 214.08 . "Q42005" 62 1
"13258P" "912" . 20393.9 11.95 273.61 . "Q42006" 63 1
"13258P" "912" . 19625.93 11.5 263.3 . "Q42007" 64 1
"13258P" "912" . 8533.01 5 114.48 . "Q42008" 65 1
"13258P" "912" .36 8533.01 5 114.48 . "Q42009" 66 1
"13258P" "912" .48 10182.21 6 139.02 . "Q42010" 67 1
"13258P" "912" .83 22838.54 13.45 318.57 . "Q42011" 68 1
"13258P" "912" .88 24621.5 14.5 352.69 . "Q42012" 69 1
"13258P" "912" .87 27508.13 16.2 419.46 . "Q42013" 70 1
"13258P" "912" 1.7 57491.29 31.55 856.45 . "Q42014" 71 1
"13258P" "912" 2.29 79449.13 43.6 1231.41 . "Q42015" 72 1
"13258P" "912" 1.69 50293.51 27.6 815.14 . "Q42016" 73 1
"13258P" "912" 1.67 53209.05 29.2 890.08 . "Q42017" 74 1
"13258P" "912" 1.51 56124.62 30.8 957.95 . "Q42018" 75 1
"13258P" "912" 1.65 70975.75 38.95 1250.58 . "Q42019" 76 1
"13258P" "912" 1.58 82933.5 42.4 1397.51 . "Q42020" 77 1
"13258P" "912" 2.31 135940.6 69.5 2345.48 . "Q42021" 78 1
"13258P" "912" 2.31 158434.4 81 2799.19 . "Q42022" 79 1
"13258P" "912" 2.07 141612.9 72.4 2590.11 . "Q42023" 80 1
"13259U" "6512" .12 4.73 74.435 5.04 . "Q12004" 1 2
"13259U" "6512" .44 52.62 84.247 5.7 . "Q12005" 2 2
"13259U" "6512" .62 152.1 89.203 6.11 . "Q12006" 3 2
"13259U" "6512" .93 336.26 98.123 6.79 . "Q12007" 4 2
"13259U" "6512" 1.38 411.04 77 5.39 . "Q12008" 5 2
"13259U" "6512" .6 138.79 26 1.83 . "Q12009" 6 2
"13259U" "6512" .69 176.16 33 2.33 . "Q12010" 7 2
"13259U" "6512" .6 128.12 24 1.69 . "Q12011" 8 2
"13259U" "6512" .5 109.97 20.6 1.65 . "Q12012" 9 2
"13259U" "6512" .84 154.27 28.9 2.31 . "Q12013" 10 2
"13259U" "6512" 1.13 193.24 36.2 2.9 . "Q12014" 11 2
"13259U" "6512" 1.03 178.56 33.45 2.68 . "Q12015" 12 2
"13259U" "6512" .51 176.16 33 2.64 . "Q12016" 13 2
"13259U" "6512" .94 291.74 29.25 2.34 . "Q12017" 14 2
"13259U" "6512" .94 275.28 27.6 2.21 . "Q12018" 15 2
"13259U" "6512" .79 285.26 28.6 2.29 . "Q12019" 16 2
"13259U" "6512" .69 295.23 29.6 2.37 . "Q12020" 17 2
"13259U" "6512" .9 417.86 29 2.32 . "Q12021" 18 2
"13259U" "6512" .83 432.27 30 2.4 . "Q12022" 19 2
"13259U" "6512" .83 432.27 30 2.4 . "Q12023" 20 2
end
label values time_numeric time_numeric
label def time_numeric 1 "Q12004", modify
label def time_numeric 2 "Q12005", modify
label def time_numeric 3 "Q12006", modify
label def time_numeric 4 "Q12007", modify
label def time_numeric 5 "Q12008", modify
label def time_numeric 6 "Q12009", modify
label def time_numeric 7 "Q12010", modify
label def time_numeric 8 "Q12011", modify
label def time_numeric 9 "Q12012", modify
label def time_numeric 10 "Q12013", modify
label def time_numeric 11 "Q12014", modify
label def time_numeric 12 "Q12015", modify
label def time_numeric 13 "Q12016", modify
label def time_numeric 14 "Q12017", modify
label def time_numeric 15 "Q12018", modify
label def time_numeric 16 "Q12019", modify
label def time_numeric 17 "Q12020", modify
label def time_numeric 18 "Q12021", modify
label def time_numeric 19 "Q12022", modify
label def time_numeric 20 "Q12023", modify
label def time_numeric 21 "Q22004", modify
label def time_numeric 22 "Q22005", modify
label def time_numeric 23 "Q22006", modify
label def time_numeric 24 "Q22007", modify
label def time_numeric 25 "Q22008", modify
label def time_numeric 26 "Q22009", modify
label def time_numeric 27 "Q22010", modify
label def time_numeric 28 "Q22011", modify
label def time_numeric 29 "Q22012", modify
label def time_numeric 30 "Q22013", modify
label def time_numeric 31 "Q22014", modify
label def time_numeric 32 "Q22015", modify
label def time_numeric 33 "Q22016", modify
label def time_numeric 34 "Q22017", modify
label def time_numeric 35 "Q22018", modify
label def time_numeric 36 "Q22019", modify
label def time_numeric 37 "Q22020", modify
label def time_numeric 38 "Q22021", modify
label def time_numeric 39 "Q22022", modify
label def time_numeric 40 "Q22023", modify
label def time_numeric 41 "Q32004", modify
label def time_numeric 42 "Q32005", modify
label def time_numeric 43 "Q32006", modify
label def time_numeric 44 "Q32007", modify
label def time_numeric 45 "Q32008", modify
label def time_numeric 46 "Q32009", modify
label def time_numeric 47 "Q32010", modify
label def time_numeric 48 "Q32011", modify
label def time_numeric 49 "Q32012", modify
label def time_numeric 50 "Q32013", modify
label def time_numeric 51 "Q32014", modify
label def time_numeric 52 "Q32015", modify
label def time_numeric 53 "Q32016", modify
label def time_numeric 54 "Q32017", modify
label def time_numeric 55 "Q32018", modify
label def time_numeric 56 "Q32019", modify
label def time_numeric 57 "Q32020", modify
label def time_numeric 58 "Q32021", modify
label def time_numeric 59 "Q32022", modify
label def time_numeric 60 "Q32023", modify
label def time_numeric 61 "Q42004", modify
label def time_numeric 62 "Q42005", modify
label def time_numeric 63 "Q42006", modify
label def time_numeric 64 "Q42007", modify
label def time_numeric 65 "Q42008", modify
label def time_numeric 66 "Q42009", modify
label def time_numeric 67 "Q42010", modify
label def time_numeric 68 "Q42011", modify
label def time_numeric 69 "Q42012", modify
label def time_numeric 70 "Q42013", modify
label def time_numeric 71 "Q42014", modify
label def time_numeric 72 "Q42015", modify
label def time_numeric 73 "Q42016", modify
label def time_numeric 74 "Q42017", modify
label def time_numeric 75 "Q42018", modify
label def time_numeric 76 "Q42019", modify
label def time_numeric 77 "Q42020", modify
label def time_numeric 78 "Q42021", modify
label def time_numeric 79 "Q42022", modify
label def time_numeric 80 "Q42023", modify
label values CompCode CompCode
label def CompCode 1 "13258P", modify
label def CompCode 2 "13259U", modify
[/CODE]

[Nick Cox]

Dates are a pain to manage in just about any software. There are so many ways to represent dates, which are easy enough for people to understand, but most have to be explained to software.

Unfortunately using encode is almost always wrong for string date variables, unless you specify a series of value labels in advance, which still is a poorer method than a direct operation on dates.

Stata has no agent inside encode capable of reading the meaning of your date strings, which in the absence of other instructions will be treated as you have noticed: according to alphanumeric sort order whereby all Q1 dates sort before all Q2 dates, and so on, which is a recipe for useless dates.

So, although encode on company name strings should be fine, so long as you do not hit a limit on the number of value labels, you do need a quite different solution for dates like yours. Here is one way, and there are others.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 time
"Q12004"
"Q22004"
"Q32004"
"Q42004"
"Q12005"
end

gen qdate = quarterly(substr(time, 2, 1) + " " + substr(time, 3, .), "QY")
format qdate %tq

list

     +-----------------+
     |   time    qdate |
     |-----------------|
  1. | Q12004   2004q1 |
  2. | Q22004   2004q2 |
  3. | Q32004   2004q3 |
  4. | Q42004   2004q4 |
  5. | Q12005   2005q1 |
     +-----------------+

For more detail see

help datetime

and https://journals.sagepub.com/doi/pdf...867X1801800413 -- which explains what you shouldn't do as well as what you should.