make variables as observation

[andrew roder]

Dear All,

Can you help me with the following : I want to make the variables mar, June, sept, dec as observation under year variable. How can I do that.

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

* Example generated by -dataex-. For more info, type help dataex
clear
input str16 country byte chapter double(mar18 jun18 sep18 dec18) float y2018 double(mar19 jun19 sep19 jun20)
"IMPORT INDIA"  1                7.2171             3.6968            7.94914           5.651436 24.514477           7.614126              7.2298           9.790684             7.1066
"IMPORT INDIA"  2           222.7710607       185.00059735         255.305723        310.4100895  973.4875        266.9343733         218.0541835       289.42461785               2.68
"IMPORT INDIA"  3             65.166835           45.48543          20.647569          38.524425 169.82426          44.093746               19.18           31.75562              .0447
"IMPORT INDIA"  4     390.4099691700003 392.52048234000006  363.5152197100003 364.23289696999944 1510.6786  423.6984869899997   362.6832425199998  390.9354584300003  580.2786581200006
"IMPORT INDIA"  5    .34395190000000003             .13164            .328165          .57187035 1.3756273           1.581937          1.29770988         1.24666493                  0
"IMPORT INDIA"  .     685.9089167700002       626.83494969  647.7458167100002  719.3907178199995 2679.8804  743.9226692899998   608.4449358999998  723.1530452100003  590.1099581200007
"IMPORT INDIA"  6               1.51446            .445035           2.767505             .56948   5.29648             .50349             1.48761           1.327412                  0
"IMPORT INDIA"  7           128.7776383 109.74251881999999        93.78128456       103.50270244  435.8041 107.30147582000001  114.37753300000003  98.18998802000004 176.66448800000003
"IMPORT INDIA"  8    25.526377860000007 45.289048539999996        27.65244829 21.249262769999998 119.71714 24.438523370000002         48.26942964        28.51305707        97.57664461
"IMPORT INDIA"  9    40.474889240000024        38.58849518 37.546502289999985 42.873509120000016  159.4834  32.64695258000002   33.56907351999999        38.90800331  92.48120076000002
"IMPORT INDIA" 10     513.9362212299999  643.2686240599999       538.00035578  562.0074630900001 2257.2126  614.7722486800002   667.0405216200003       611.67204584 1030.4300065500001
"IMPORT INDIA" 11     81.92072240000003 101.96127513000002  91.23417904000001 106.00628846999996  381.1225  93.52212866000004   97.38155317999994 113.21893123000008       130.70160057
"IMPORT INDIA" 12           21.24503613 27.886255379999998        18.58064116        20.18459126  87.89652        20.28224484         21.21562689        24.39768662 40.737054289999996
"IMPORT INDIA" 13    1.8289510300000003         2.48873586         1.32980754 2.5392012999999998  8.186696         1.24538937          2.22170327         1.42048905         1.96424983
"IMPORT INDIA" 14           30.48622489 33.642402090000004        29.66174865         23.6469033 117.43728          20.871497          32.9219455 30.658872199999998           7.106942
"IMPORT INDIA"  .     845.7105210799999 1003.3123900599999  840.5544723100002       882.57940175  3572.157  915.5839503200002  1018.4849966200003  948.3064853400002 1577.6621866100002
"IMPORT INDIA" 15     289.1446985300002  290.5813990399999  333.5075980200002 286.12024157999997 1199.3539 302.75919010999985   338.3162408600002  315.5188735200001  546.3167738700001
"IMPORT INDIA"  .     289.1446985300002  290.5813990399999  333.5075980200002 286.12024157999997 1199.3539 302.75919010999985   338.3162408600002  315.5188735200001  546.3167738700001
"IMPORT INDIA" 16             1.4931698 2.6483625099999997         1.96698806         2.04300303  8.151524         2.37530188  1.2057263900000001          3.6559246         1.02807529
"IMPORT INDIA" 17    148.47770225000005       135.47112997 132.76525488000004 103.97532050000002  520.6894 138.33882206000004  135.70783283999995       134.56367279 134.41080650999993
"IMPORT INDIA" 18            5.08346416         5.68224568 3.0879188699999998  4.816595529999999 18.670225         5.49116041   5.450979420000001 2.7554576400000013         5.05741136
"IMPORT INDIA" 19    197.80070349000013  185.0929461599999 175.75362762000006 195.96333475999992  754.6106 211.53409563000014  195.85891056000014  210.5498274999999 212.82045650000012
"IMPORT INDIA" 20     44.13671925000003  64.09288078999998 51.358988370000006  36.17437460000001 195.76297 60.053856739999986   61.38908625000002  55.42878629999999        49.01079011
"IMPORT INDIA" 21     67.44107133000001  63.22327622000005  64.19195532999996  65.12787818999998  259.9842  70.61834663999997   75.38385256999986  70.30943055999998  80.29421598000003
"IMPORT INDIA" 22     33.99319494000001 27.436389540000004  32.72867461000001        37.18849254 131.34676 27.726487740000003  39.033123090000004  40.94940791000001 15.622959309999999
"IMPORT INDIA" 23    123.61014920000001 106.85803356999998 105.27346189999999       129.21426307  464.9559       112.03114169        114.87091017 101.90080198999999 141.80123059999997
"IMPORT INDIA" 24              .1477845            .179772           .0828225 .10401189999999999  .5143909             .02899            .0852275            .080491         2.02404112
"IMPORT INDIA"  .     622.1839589200001  590.6850364399999  567.2096921400001       574.60727412  2354.686  628.1982027900002        628.98564879  620.1938002899999       642.06998678
"IMPORT INDIA" 25          266.01622283       313.98337292       199.00583371 211.14006584999993  990.1455       229.96480691   273.3085853299999       228.02703536  70.24961496999997
"IMPORT INDIA" 26           153.4174442 122.06629552999999  90.52611170000002        73.07022966  439.0801  52.58243066000001  110.34844372000002  65.19791665000002  39.94353219999999
"IMPORT INDIA" 27    3720.2363789600167 3721.2648853400065  3518.382098059985 3716.8749095800263 14676.758  3396.864794599973  3530.8077223700466  3103.689652860023 2166.2346109000005
"IMPORT INDIA"  .     4139.670045990017  4157.314553790006  3807.914043469985  4001.085205090026 16105.983  3679.412032169973  3914.4647514200465  3396.914604870023 2276.4277580700004
"IMPORT INDIA" 28    20.556010849999993 21.731848359999997 22.295948560000003        21.05295915  85.63676 20.204073649999998  21.480201970000007 22.643543849999993  20.99693332999999
"IMPORT INDIA" 29     18.95274242000001  20.73714640000001        17.13721502 22.097774989999998  78.92488 13.900326549999999  18.511502000000004 13.830947650000004  7.684851480000001
"IMPORT INDIA" 30           61.23180421 46.346970569999996  65.03395517000001 131.23065291000003 303.84338  95.00725110999994   70.19465811000002  82.03169223000002  87.96884475000003
"IMPORT INDIA" 31           29.74603128         8.00066738         6.69786016        22.19446195  66.63902        34.59275705         16.42769162  7.416673230000001        13.12698505
"IMPORT INDIA" 32     70.97117081999997  84.88725249999997        94.82496391  90.71633883000003  341.3997  68.02933523999998  102.10493743999997  99.18586217000001  87.88108952000002
"IMPORT INDIA" 33     80.90668100000012  92.72519509000004  79.81909443999996        76.49594444  329.9469  80.95524401000002  102.73373332000004   90.6634148100001  72.68523396000003
"IMPORT INDIA" 34           89.61752918  84.33473429000003  82.48660321000004   82.2719773900001 338.71085  76.95954652999993    96.9605328799999  86.56810360999991 103.93112016999997
"IMPORT INDIA" 35    13.887465659999997 18.488057200000007 16.839396100000005 15.516088500000006  64.73101 11.595229530000005         18.92368916         16.9431205        11.23573033
"IMPORT INDIA" 36    26.877460199999998            23.9687         22.5242435          19.464211  92.83462          23.038414         29.48416616 12.395635310000001        19.62936811
"IMPORT INDIA" 37            3.29199239 1.0898091099999998 1.1404751400000002          11.944862 17.467138  .9308873300000001           .55487178         1.13366963         1.23159572
"IMPORT INDIA" 38    134.25671906000002       128.73289032 146.39712036000003 140.31855885000002 549.70526 124.10295151000004        156.75301883 133.10054616000002       161.72036728
"IMPORT INDIA"  .     550.2956070700002  531.0432712200001       555.19687557  633.3038300100002 2269.8396  549.3160165099998   634.1290032699999  565.9132091500001        588.0921197
"IMPORT INDIA" 39     344.9414346799998  429.2023020200002 405.89381384999984 343.05628893999983  1523.094 327.65164919000006   460.9590786799999  431.2239493599999  416.8329011200001
"IMPORT INDIA" 40    251.83732442999982  297.3337700699998 251.76262107999975 296.91434215999993  1097.848  316.5762405699996   339.9557517000001  307.9869006700003 216.49339665000016
"IMPORT INDIA"  .     596.7787591099997  726.5360720900001  657.6564349299996  639.9706310999998  2620.942  644.2278897599997        800.91483038  739.2108500300002  633.3262977700002
"IMPORT INDIA" 41                .01032             .00108            .346716                  0   .358116          .00022264             .001944                  0               .006
"IMPORT INDIA" 42            7.38834814  4.040030789999999         3.68389076         6.47510285  21.58737         7.58577566          3.77952802 3.3595799000000004         2.49984372
"IMPORT INDIA" 43                     0                  0                  0                  0         0                  0                   0                  0                  0
"IMPORT INDIA"  .            7.39866814  4.041110789999999         4.03060676         6.47510285  21.94549          7.5859983          3.78147202 3.3595799000000004 2.5058437199999997
"IMPORT INDIA" 44     619.6199851100001  666.2958625300001  660.5759865800003       554.72030036  2501.212       549.61444128   524.5075462399999 505.18087811999993  678.6665884600001
"IMPORT INDIA" 45 .00045913999999999997            .021311          .00088619              .0437 .06635633              .0016             .000245            .005566             .00863
"IMPORT INDIA" 46    1.1015518999999998 1.0672800500000001          .63713138  .5391894199999999  3.345153          .46499231           .33295802           .3864918            .277425
"IMPORT INDIA"  .     620.7219961500001  667.3844535800001  661.2140041500003  555.3031897799999 2504.6235  550.0810335900001   524.8407492599998  505.5729359199999  678.9526434600001
"IMPORT INDIA" 47                 .0148             .03574            .270485            .110325    .43135           .0785166           .06114331              .0564             .00792
"IMPORT INDIA" 48          193.31901223 148.38701308999998 153.61789429000004 137.53975025000003  632.8636 177.43096420999998   148.0278720599999 137.05623297999998 100.18934920000002
"IMPORT INDIA" 49           30.65017828        43.33157048 29.307041980000005        65.38005029 168.66884        37.27004824   44.28134941999999 28.150454699999994          5.3918976
"IMPORT INDIA"  .          223.98399051       191.75432357 183.19542127000005 203.03012554000003  801.9639 214.77952904999998   192.3703647899999 165.26308767999996 105.58916680000002
"IMPORT INDIA" 50            12.0374395          18.102857         18.7395573         18.5725704  67.45242 13.326061170000003  15.524923800000002           15.97365         19.7577976
"IMPORT INDIA" 51            3.92179232          2.8349964           3.942983        10.32635475 21.026127         3.40876619          4.26725812           3.592192          1.4298132
"IMPORT INDIA" 52     62.98838407000004 29.628070580000006        44.77786041  35.78946561000001 173.18378  42.93569573999998         25.13002854        24.84034746 26.395024650000003
"IMPORT INDIA" 53             .14729825  .7714647299999999          .29752305             .22848  1.444766            .356896           .56747545          .28123261           1.566542
"IMPORT INDIA" 54            8.85002477        10.51973073  9.232713469999998  9.527362740000001 38.129833  9.629033260000002   9.563368909999998        10.48334581        10.11928343
"IMPORT INDIA" 55    15.845463379999993 18.314750440000005 21.037566139999996 14.414162429999996 69.611946        18.95662132         17.88879479        20.12120788 15.718353919999998
"IMPORT INDIA" 56     7.949695650000001  5.943026939999998         7.90264883  5.690154539999999 27.485525  4.459882530000002   8.233444949999999  4.584154459999999  5.005841650000001
"IMPORT INDIA" 57              1.274229 2.4610319799999996 2.1946639599999997         2.99750317  8.927428          1.6834965   5.459198859999999         6.88570642 2.8772187999999996
"IMPORT INDIA" 58    1.2470423599999998  .9223396999999999           .8389896          .61262552  3.620997  .6012705899999999           1.0346767            .212554           1.251132
"IMPORT INDIA" 59            4.67701313         3.49348957 3.9773990099999996         4.43863938  16.58654 3.6647870100000004          7.06271193  4.066480919999999  8.875361300000002
"IMPORT INDIA" 60              .1346795          .81324769 .34865559999999995          .21153546 1.5081183            .716634             .829119  .6397589699999999         3.62007864
"IMPORT INDIA" 61           11.83939924 19.223022190000002  7.655249199999999 21.925062990000004  60.64273 14.851747490000001  20.533451710000005  8.093889260000001        23.46860682
"IMPORT INDIA" 62           16.14443505 12.361709439999998 12.546195759999998 26.431439570000002  67.48378         8.67891942         33.52611169 12.288403250000004 22.278642079999994
"IMPORT INDIA" 63    30.484656379999997        36.62900364        34.96734249 34.532931010000006 136.61394 38.432145090000006  36.683648940000005        36.47763508 31.942952679999998
"IMPORT INDIA"  .    177.54155260000005       162.01874103 168.45934781999998       185.69828757   693.718 161.70195630999999  186.30421339000003 148.54055812000001 174.30664876999998
"IMPORT INDIA" 64     58.16693430999998 44.463972210000016        35.38601772        38.57793373 176.59486  68.51757649999998  30.171051589999998 29.596337809999998 37.142176129999996
"IMPORT INDIA" 65            1.46096774         1.06963499         1.53195703         1.46677155  5.529331          .91632682          1.09242823         1.25613447          .76812487
"IMPORT INDIA" 66               .843704           .6529695            .884319             .71321 3.0942025  .9840978999999999   .6837470699999999            .750592           1.464877
"IMPORT INDIA" 67                     0            .004649           .0033554            .002268  .0102724               .441             .004048              .0125            .001495
"IMPORT INDIA"  .    60.471606049999984  46.19122570000001 37.805649149999994  40.76018327999999 185.22867  70.85900121999997  31.951274889999997 31.615564279999997          39.376673
"IMPORT INDIA" 68     81.03658812999996 115.09297983000006       133.91917764       118.18578923  448.2345  84.46672726000001  109.40866896000001 117.65131318999998 54.716941219999974
"IMPORT INDIA" 69           94.04032748 113.43771124999996 113.90961757999999 107.75240439999999  429.1401 100.22561345999999        133.56719663 138.93032632999993 102.06316312999996
"IMPORT INDIA" 70     92.59062734999996 108.49288086999992 117.84415780999997 117.75326937999989  436.6809 100.93584718999995        127.85736389 115.87369156999992  69.55092029000004
"IMPORT INDIA"  .    267.66754295999993 337.02357194999996  365.6729530299999  343.6914630099999 1314.0555 285.62818790999995        370.83322948 372.45533108999985 226.33102463999995
"IMPORT INDIA" 71               .139048            .414783 .27377313000000003            .033519  .8611231            .154291             .386832            .370432                .12
"IMPORT INDIA"  .               .139048            .414783 .27377313000000003            .033519  .8611231            .154291             .386832            .370432                .12
"IMPORT INDIA" 72    1071.0871790000003 1055.2165109399996  1143.061134810001 1505.2364391200003  4774.601      1490.37007211  1320.8455203500007 1320.2503371900004 1027.3086206399996
"IMPORT INDIA" 73    492.44885281000006  647.3713818099999  476.0501965799995  356.8685329099998  1972.739 371.35727131999977   565.5101473900003 368.98599126999966 351.23694305000015
"IMPORT INDIA" 74     35.70482965999999         16.2567751         33.1159424        21.92220595 106.99976        19.70652901  18.246715860000002        22.20946148        11.96179391
"IMPORT INDIA" 75              .0690988            .124324            .321514  .5431585999999999 1.0580955             .03419 .057367699999999994            .013979            .319859
"IMPORT INDIA" 76     46.81377476999998        46.42843302        56.98024778  57.87662540999999 208.09908  43.32517110999999   63.45259539000002  58.85040712999999         67.0501059
"IMPORT INDIA" 77                     .                  .                  .                  .         .                  .                   .                  .                  .
"IMPORT INDIA" 78             .15746276  .6855998000000001 1.1431943999999998            .022615  2.008872             .81359           .01181198  .5915885500000001            .000313
"IMPORT INDIA" 79             .02849156          .34771268            .137188            .041204 .55459625             .06652            .1433116           .5544606          .00812864
"IMPORT INDIA" 80                 .0019            .296604            .049698          .00618548  .3543875            .038401              .03605                  0             .00084
"IMPORT INDIA" 81                2.4312            6.70242            4.84311            1.67846  15.65519            .023875             7.98428             .02479           1.874165
"IMPORT INDIA" 82    22.410715479999997  31.48317373000001 29.254177559999984  33.37998115999998 116.52805 40.166538199999984   38.89252753000002 28.595196039999983 20.562804829999997
"IMPORT INDIA" 83    32.859126950000004  48.41764461999999  48.00124213999998  61.35329413999999  190.6313         41.4201787         53.86968594  54.32315577999998        39.10934645
"IMPORT INDIA"  .    1704.0126317900003 1853.3305796999994 1792.9576456700004 2038.9287017700005  7389.229 2007.3223364499995   2069.050013740001 1854.3993670400002 1519.4329204199996
"IMPORT INDIA" 84    1758.7683974000013 1241.5085263099995      1199.17806975  943.2895222699999  5142.745  993.4604480199987   887.6760921900005  844.0556459500001  900.6441608199993
"IMPORT INDIA" 85    1125.7393397400003  719.3492094800013  869.0822287699991 1376.8284991499995  4090.999 1018.1836998399992   924.8205775500011  1037.693580489999  959.9307451200003
end

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


Thank you

[Nick Cox]

This needs more explanation before code can be suggested with complete confidence.

chapter is often missing

On the face of it the y variables just contain annual totals and can be ignored.

This is a brutal solution ignoring observations with missing values of chapter. Any reshape will otherwise fail unless that is fixed.

drop if chapter == . 

drop y???? 

reshape long mar jun sep dec , i(country chapter) j(year) 

rename (mar jun sep dec) (x1 x2 x3 x4) 

reshape long x, i(country chapter year) j(quarter)

gen qdate = yq(2000 + year, quarter)
format qdate %tq