Reshaping With Multiple Stubs and Years

Jared Greathouse

Join Date: Sep 2021
Posts: 2170

Reshaping With Multiple Stubs and Years

18 Dec 2022, 12:11

Say we have prod_ cai_ as variables, jan = january, and 19=the year 2019. Here, we can just do (NOTE: I use -greshape- from ssc, but if you don't have it, just remove the g)

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input long id str24 setor str23 base double(cai_jan19 prod_jan19)
  1 "VEICULOS" "GALIA"                          163769.69          0
  2 "VEICULOS" "MATA"                            15774.58     296.13
  3 "VEICULOS" "MATA"                            30615.21          0
  4 "VEICULOS" "GABARITO"                               0          0
  5 "VEICULOS" "MATA"                              177381          0
  6 "VEICULOS" "MATA"                                   0          0
  7 "VEICULOS" "CABRA"                                  0          0
  8 "VEICULOS" "MATARE"                           14112.6          0
  9 "VEICULOS" "MATA"                               28000     282.76
 10 "VEICULOS" "GALIA"                               3240          0
 11 "VEICULOS" "JAGUARE"                            11940        .75
 12 "VEICULOS" "GABARITO"                               0          0
 13 "VEICULOS" "GALIA"                                  0          0
 14 "VEICULOS" "GALIA"                               2500       81.6
 15 "VEICULOS" "ROMA"                                   0          0
 16 "VEICULOS" "MATA"                                4200          0
 17 "VEICULOS" "TOMATE"                                 0          0
 18 "VEICULOS" "MATA"                             5869.18          0
 19 "VEICULOS" "BATIRA"                                 0          0
 20 "VEICULOS" "MATA"                             5829.77          0
 21 "VEICULOS" "MARE"                                   0          1
 22 "VEICULOS" "GALIA"                                  0          0
 23 "VEICULOS" "TOMATE"                              9900          0
 24 "VEICULOS" "RIO"                                 2100          0
 25 "VEICULOS" "MATA"                                2900      72.08
 26 "VEICULOS" "TOSCANA"                                0        6.8
 27 "VEICULOS" "MATA"                                   0          0
 28 "VEICULOS" "MATA"                                   0          0
 29 "VEICULOS" "MATA"                              8342.7      55.76
 30 "VEICULOS" "TOMATE"                                 0          0
 31 "VEICULOS" "TOMATE"                                 0          0
 32 "VEICULOS" "GALIA"                               2000          0
 33 "VEICULOS" "GABARITO"                               0          0
 34 "VEICULOS" "MATA"                                2500          0
 35 "VEICULOS" "GALIA"                                  0          0
 36 "VEICULOS" "MATA"                                   0          0
 37 "VEICULOS" "TOMATE"                                 0          0
 38 "VEICULOS" "MATA"                                   0          0
 39 "VEICULOS" "CASTELO"                                0          0
 40 "VEICULOS" "GABARITO"                               0          0
 41 "VEICULOS" "MATA"                                   0          0
 42 "VEICULOS" "JERONIMO MONTEIRO"                      0          0
 43 "VEICULOS" "IUNA"                                   0          0
 44 "VEICULOS" "RIO"                                    0          0
 45 "VEICULOS" "MATA"                                   0          0
 46 "VEICULOS" "GALIA"                                  0          0
 47 "VEICULOS" "TOMATE"                                 0          0
 48 "VEICULOS" "GABARITO"                               0          0
 49 "VEICULOS" "TOMATE"                       36270243.93 2499024.78
 50 "VEICULOS" "GALIA"                        79199672.03   33379.91
 51 "VEICULOS" "GALIA"                        40256959.75    1947.84
 52 "VEICULOS" "GABARITO"                     56481852.68    13188.3
 53 "VEICULOS" "MATA"                         28227341.05     141.38
 54 "VEICULOS" "VIANA"                         7503199.01  306873.45
 55 "VEICULOS" "GALIA"                        12207840.41    5988.21
 56 "VEICULOS" "CABRA"                         6234068.45     324.44
 57 "VEICULOS" "CABRA"                        21321269.36    10268.4
 58 "VEICULOS" "MATA"                         25347361.82    9430.33
 59 "VEICULOS" "MATA"                          37834456.5          0
 60 "VEICULOS" "CABRA"                         3971939.93     994.38
 61 "VEICULOS" "GALIA"                        49177108.44     3075.5
 62 "VEICULOS" "MATA"                         29395929.44    4369.68
 63 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"      16523362.43    5106.09
 64 "VEICULOS" "GABARITO"                     19730699.01    3131.14
 65 "VEICULOS" "GALIA"                        24992842.34    6986.03
 66 "VEICULOS" "GALIA"                        26505195.77    8184.27
 67 "VEICULOS" "GABARITO"                     22921225.95   15805.08
 68 "VEICULOS" "GABARITO"                     10976119.68          0
 69 "VEICULOS" "MATA"                         21793461.58    7145.22
 70 "VEICULOS" "CABRA"                         3089946.79     431.39
 71 "VEICULOS" "ROMA"                         10741864.95    1812.09
 72 "VEICULOS" "TOMATE"                        6330086.16     509.64
 73 "VEICULOS" "GALIA"                                  0          0
 74 "VEICULOS" "ROMA"                         10645811.51    5437.15
 75 "VEICULOS" "ROMA"                          9520449.29     2070.1
 76 "VEICULOS" "GABARITO"                      9783409.42    2335.81
 77 "VEICULOS" "CABRA"                          3993076.2   90842.05
 78 "VEICULOS" "GALIA"                        32075359.46    2376.79
 79 "VEICULOS" "MATA"                          8513524.74    1074.11
 80 "VEICULOS" "GALIA"                         9735425.28    1179.26
 81 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       9761198.56    4520.34
 82 "VEICULOS" "GALIA"                        12178331.94     899.57
 83 "VEICULOS" "GALIA"                        12161583.23      84.34
 84 "VEICULOS" "GABARITO"                      5907479.18   20014.72
 85 "VEICULOS" "CABRA"                         4587921.79    4611.62
 86 "VEICULOS" "GALIA"                        17878251.51    7978.31
 87 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       8260480.72    16887.7
 88 "VEICULOS" "CABRA"                   8993436.55000001    3710.43
 89 "VEICULOS" "GALIA"                          8615907.5     682.74
 90 "VEICULOS" "MATA"                         18194895.04     3763.8
 91 "VEICULOS" "MATA"                          4772730.35    2952.94
 92 "VEICULOS" "GABARITO"                     14576640.84   13914.32
 93 "VEICULOS" "SAO MATEUS"                    7011158.43     967.76
 94 "VEICULOS" "ROMA"                          5604126.25    1084.69
 95 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       5586772.71     149.76
 96 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       2504687.41    1734.81
 97 "VEICULOS" "CABRA"                         9864328.82       7148
 98 "VEICULOS" "CABRA"                         5195855.39    2137.39
 99 "VEICULOS" "VENDA NOVA DO IMIGRANTE"       2465742.24      94.99
100 "VEICULOS" "GALIA"                         5675943.51    7339.68
end

unab stubs : *19
local stubs : subinstr local stubs "19" "", all
greshape long `stubs', i(id) j(Year)

replace Year = 2000+Year

greshape long cai_ prod_, i(id) j(month) string

br

to -reshape- and boom boom, reshape complete. But what if we've got more variable stubs, months, and years like this

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input long id str24 setor str23 base double(cai_jan19 prod_jan19 cai_fev19 prod_fev19 cai_jul22 prod_jul22)
  1 "VEICULOS" "GALIA"                          163769.69          0         265589.1         0            0                0
  2 "VEICULOS" "MATA"                            15774.58     296.13          14837.1    189.77     37416.43                0
  3 "VEICULOS" "MATA"                            30615.21          0         57376.13         0     37882.93            89.87
  4 "VEICULOS" "GABARITO"                               0          0                0         0            0                0
  5 "VEICULOS" "MATA"                              177381          0         212875.5         0            0                0
  6 "VEICULOS" "MATA"                                   0          0                0         0            0                0
  7 "VEICULOS" "CABRA"                                  0          0                0         0            0                0
  8 "VEICULOS" "MATARE"                           14112.6          0              650    193.02            0           583.69
  9 "VEICULOS" "MATA"                               28000     282.76            24800     439.2            0                0
 10 "VEICULOS" "GALIA"                               3240          0             3420         0     40101.25            676.6
 11 "VEICULOS" "JAGUARE"                            11940        .75            10516         0            0                0
 12 "VEICULOS" "GABARITO"                               0          0                0         0         7282                0
 13 "VEICULOS" "GALIA"                                  0          0                0         0            0                0
 14 "VEICULOS" "GALIA"                               2500       81.6           990.95         0            0                0
 15 "VEICULOS" "ROMA"                                   0          0                0         0            0                0
 16 "VEICULOS" "MATA"                                4200          0             4200         0            0                0
 17 "VEICULOS" "TOMATE"                                 0          0                0         0         1897                0
 18 "VEICULOS" "MATA"                             5869.18          0                0         0            0                0
 19 "VEICULOS" "BATIRA"                                 0          0                0         0            0                0
 20 "VEICULOS" "MATA"                             5829.77          0          6555.37         0            0                0
 21 "VEICULOS" "MARE"                                   0          1                0         0     16994.75            39.44
 22 "VEICULOS" "GALIA"                                  0          0                0         0            0                0
 23 "VEICULOS" "TOMATE"                              9900          0                0    161.84            0                0
 24 "VEICULOS" "RIO"                                 2100          0             1000         0        750.2                0
 25 "VEICULOS" "MATA"                                2900      72.08             1900     41.76            0                0
 26 "VEICULOS" "TOSCANA"                                0        6.8                0         0            0                0
 27 "VEICULOS" "MATA"                                   0          0                0         0            0                0
 28 "VEICULOS" "MATA"                                   0          0            13900         0            0                0
 29 "VEICULOS" "MATA"                              8342.7      55.76          8950.82     84.96            0                0
 30 "VEICULOS" "TOMATE"                                 0          0                0         0        66900            200.6
 31 "VEICULOS" "TOMATE"                                 0          0                0         0            0                0
 32 "VEICULOS" "GALIA"                               2000          0             2000     91.84            0                0
 33 "VEICULOS" "GABARITO"                               0          0                0         0            0                0
 34 "VEICULOS" "MATA"                                2500          0              500         0            0                0
 35 "VEICULOS" "GALIA"                                  0          0                0         0            0                0
 36 "VEICULOS" "MATA"                                   0          0                0         0         9000                0
 37 "VEICULOS" "TOMATE"                                 0          0                0         0        26500            129.2
 38 "VEICULOS" "MATA"                                   0          0                0         0        15000                0
 39 "VEICULOS" "CASTELO"                                0          0                0         0      1719.49                0
 40 "VEICULOS" "GABARITO"                               0          0                0         0            0                0
 41 "VEICULOS" "MATA"                                   0          0                0         0            0                0
 42 "VEICULOS" "JERONIMO MONTEIRO"                      0          0                0         0            0                0
 43 "VEICULOS" "IUNA"                                   0          0                0         0            0                0
 44 "VEICULOS" "RIO"                                    0          0                0         0            0                0
 45 "VEICULOS" "MATA"                                   0          0                0         0            0                0
 46 "VEICULOS" "GALIA"                                  0          0                0         0            0                0
 47 "VEICULOS" "TOMATE"                                 0          0                0         0            0                0
 48 "VEICULOS" "GABARITO"                               0          0                0         0            0                0
 49 "VEICULOS" "TOMATE"                       36270243.93 2499024.78      69927348.97 982860.51 139823383.96       2133616.24
 50 "VEICULOS" "GALIA"                        79199672.03   33379.91 91442922.1400001  32398.71  99044951.08       1955510.53
 51 "VEICULOS" "GALIA"                        40256959.75    1947.84      40637036.55   5242.71  64180953.64        945622.43
 52 "VEICULOS" "GABARITO"                     56481852.68    13188.3      56105617.99  16760.07  61467938.52       1164673.43
 53 "VEICULOS" "MATA"                         28227341.05     141.38      30397580.33     22.87  54807917.93        153541.02
 54 "VEICULOS" "VIANA"                         7503199.01  306873.45      15204214.68 453458.88  53058326.07       1747509.94
 55 "VEICULOS" "GALIA"                        12207840.41    5988.21      11738571.85   5178.41  51410770.05        933845.43
 56 "VEICULOS" "CABRA"                         6234068.45     324.44      10303670.33    114.61  44126375.91         53189.82
 57 "VEICULOS" "CABRA"                        21321269.36    10268.4      23085009.03   4403.57  40292533.87       1033701.18
 58 "VEICULOS" "MATA"                         25347361.82    9430.33      26796727.42   7834.77   39595203.9         90885.68
 59 "VEICULOS" "MATA"                          37834456.5          0      67997224.89         0  36859472.24                0
 60 "VEICULOS" "CABRA"                         3971939.93     994.38       3105014.11   1950.73  35269189.87                0
 61 "VEICULOS" "GALIA"                        49177108.44     3075.5      41755482.61  13914.97  35073935.62        157457.86
 62 "VEICULOS" "MATA"                         29395929.44    4369.68      21643446.52   7246.95  34869477.48        879118.61
 63 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"      16523362.43    5106.09      16098753.03   8834.22  33556600.77        746286.84
 64 "VEICULOS" "GABARITO"                     19730699.01    3131.14      25276917.96   1285.69  32834894.16 81177.3700000001
 65 "VEICULOS" "GALIA"                        24992842.34    6986.03      26045192.37  16226.48  30263601.09         66141.87
 66 "VEICULOS" "GALIA"                        26505195.77    8184.27      25719094.13  14901.45  26909556.11         60607.36
 67 "VEICULOS" "GABARITO"                     22921225.95   15805.08      16681291.29  12047.32  25136335.93        581936.77
 68 "VEICULOS" "GABARITO"                     10976119.68          0       5272851.73   4723.81  24351619.38         69041.61
 69 "VEICULOS" "MATA"                         21793461.58    7145.22      21123027.49   8724.02   22040447.6        100088.45
 70 "VEICULOS" "CABRA"                         3089946.79     431.39       2902018.28    349.39  21572576.25         55690.74
 71 "VEICULOS" "ROMA"                         10741864.95    1812.09      11018529.77    3671.4  21364365.17        431222.12
 72 "VEICULOS" "TOMATE"                        6330086.16     509.64       6608193.43    417.87  20767201.62        112578.19
 73 "VEICULOS" "GALIA"                                  0          0                0         0  19821327.44        345478.35
 74 "VEICULOS" "ROMA"                         10645811.51    5437.15      10115628.79  20681.32  19334625.18         75616.23
 75 "VEICULOS" "ROMA"                          9520449.29     2070.1       6068971.92   6406.94  19262529.99        328732.44
 76 "VEICULOS" "GABARITO"                      9783409.42    2335.81       8828413.53   3500.97  18799004.03         29372.99
 77 "VEICULOS" "CABRA"                          3993076.2   90842.05        5443790.1   2928.53  18734259.31           385231
 78 "VEICULOS" "GALIA"                        32075359.46    2376.79      39136760.08   6845.56  18121559.78         49191.44
 79 "VEICULOS" "MATA"                          8513524.74    1074.11       7912980.48   4009.34  17530720.85         95684.67
 80 "VEICULOS" "GALIA"                         9735425.28    1179.26       9261581.91         0  17237798.06         57485.03
 81 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       9761198.56    4520.34       6422876.68   5327.63  16723890.68         30380.09
 82 "VEICULOS" "GALIA"                        12178331.94     899.57      11265011.25    406.24  16528948.81          40726.4
 83 "VEICULOS" "GALIA"                        12161583.23      84.34       8503122.14    133.15  14971406.18         13885.02
 84 "VEICULOS" "GABARITO"                      5907479.18   20014.72       6636078.65  27129.43  14326268.03         61730.95
 85 "VEICULOS" "CABRA"                         4587921.79    4611.62       5332246.66   2999.05  14118387.61         67885.32
 86 "VEICULOS" "GALIA"                        17878251.51    7978.31      18290305.43   5730.71  14099024.64         39790.46
 87 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       8260480.72    16887.7       8676673.11    622.81   13947755.6        182718.32
 88 "VEICULOS" "CABRA"                   8993436.55000001    3710.43       6969507.33   4612.46  13923164.14                0
 89 "VEICULOS" "GALIA"                          8615907.5     682.74       7408275.52     589.6  13219782.87           252.91
 90 "VEICULOS" "MATA"                         18194895.04     3763.8      13621800.81   3595.49  12776045.45        204405.56
 91 "VEICULOS" "MATA"                          4772730.35    2952.94       4096693.34    132.22  12606368.12         66014.67
 92 "VEICULOS" "GABARITO"                     14576640.84   13914.32      10185506.15  13996.58  12338430.94          90071.7
 93 "VEICULOS" "SAO MATEUS"                    7011158.43     967.76       4662489.89   3065.71  10970791.04          4887.02
 94 "VEICULOS" "ROMA"                          5604126.25    1084.69        5891984.6   1137.33  10673795.58         11669.25
 95 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       5586772.71     149.76       3874129.96      8.08  10050033.55        290624.83
 96 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       2504687.41    1734.81       3693058.93  11182.55   9726815.61                0
 97 "VEICULOS" "CABRA"                         9864328.82       7148      11417812.71   6080.46   9467304.22         38895.85
 98 "VEICULOS" "CABRA"                         5195855.39    2137.39       5535605.43   3768.05   9366446.47         16157.73
 99 "VEICULOS" "VENDA NOVA DO IMIGRANTE"       2465742.24      94.99       2552504.47       245    8711207.1         84562.71
100 "VEICULOS" "GALIA"                         5675943.51    7339.68       6047564.62   3116.77   8504805.93         45185.19
end

br

Do I need........ 4 -reshape-'s in this case? What if I had monthly obs from from 2000 to today, how would I generalize this solution to other similar situations?

Tags: None

Clyde Schechter

Join Date: Apr 2014

Posts: 30075
#2

18 Dec 2022, 12:34

No, a single reshape will do it. The following code will work with any number of variables and months:

Code:

ds *_jan19 local stubs `r(varlist)' local stubs: subinstr local stubs "_jan19" "", all greshape long `stubs', i(id) j(month) string replace month = subinstr(month, "_", "", .) gen int year = 2000 + real(substr(month, -2, .)) replace month = substr(month, 1, 3)

Note: I don't know what to do with the month variable from this point. It is in a foreign language--perhaps Spanish? Anyway, Stata's date commands don't seem to accept those values: otherwise I would have just created a Stata monthly date variable from the original month variable right after -replace month = subinstr(month, "_", "", .)-. I guess you can just create a value label with all of the Spanish abbreviations of the months of the year and use that to -encode month ...- and then create a monthly variable with -ym()-.

Added: This code assumes that all of the possible *_mmmyy variables are instantiated as *_jan19, since it uses the *_jan19 series to create the list of stubs. If that isn't true of jan19 but is true of some other mmmyy, use the other one. If it isn't true of any of them then you might have to resort to making a list of all of them, removing the suffixes from all of them, and then removing duplicates from the list.

Last edited by Clyde Schechter; 18 Dec 2022, 12:39.
Comment

Jared Greathouse

Join Date: Sep 2021
Posts: 2170

18 Dec 2022, 12:47

Portuguese. I don't know either, someone asked me how to do it and I didn't have the foggiest idea how to tackle it. I'm posting more commented code with print debugging so others can follow it, should they ever need it. Thanks a lot!

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input long id str24 setor str23 base double(cai_jan19 prod_jan19 cai_fev19 prod_fev19 cai_jul22 prod_jul22)
  1 "VEICULOS" "GALIA"                          163769.69          0         265589.1         0            0                0
  2 "VEICULOS" "MATA"                            15774.58     296.13          14837.1    189.77     37416.43                0
  3 "VEICULOS" "MATA"                            30615.21          0         57376.13         0     37882.93            89.87
  4 "VEICULOS" "GABARITO"                               0          0                0         0            0                0
  5 "VEICULOS" "MATA"                              177381          0         212875.5         0            0                0
  6 "VEICULOS" "MATA"                                   0          0                0         0            0                0
  7 "VEICULOS" "CABRA"                                  0          0                0         0            0                0
  8 "VEICULOS" "MATARE"                           14112.6          0              650    193.02            0           583.69
  9 "VEICULOS" "MATA"                               28000     282.76            24800     439.2            0                0
 10 "VEICULOS" "GALIA"                               3240          0             3420         0     40101.25            676.6
 11 "VEICULOS" "JAGUARE"                            11940        .75            10516         0            0                0
 12 "VEICULOS" "GABARITO"                               0          0                0         0         7282                0
 13 "VEICULOS" "GALIA"                                  0          0                0         0            0                0
 14 "VEICULOS" "GALIA"                               2500       81.6           990.95         0            0                0
 15 "VEICULOS" "ROMA"                                   0          0                0         0            0                0
 16 "VEICULOS" "MATA"                                4200          0             4200         0            0                0
 17 "VEICULOS" "TOMATE"                                 0          0                0         0         1897                0
 18 "VEICULOS" "MATA"                             5869.18          0                0         0            0                0
 19 "VEICULOS" "BATIRA"                                 0          0                0         0            0                0
 20 "VEICULOS" "MATA"                             5829.77          0          6555.37         0            0                0
 21 "VEICULOS" "MARE"                                   0          1                0         0     16994.75            39.44
 22 "VEICULOS" "GALIA"                                  0          0                0         0            0                0
 23 "VEICULOS" "TOMATE"                              9900          0                0    161.84            0                0
 24 "VEICULOS" "RIO"                                 2100          0             1000         0        750.2                0
 25 "VEICULOS" "MATA"                                2900      72.08             1900     41.76            0                0
 26 "VEICULOS" "TOSCANA"                                0        6.8                0         0            0                0
 27 "VEICULOS" "MATA"                                   0          0                0         0            0                0
 28 "VEICULOS" "MATA"                                   0          0            13900         0            0                0
 29 "VEICULOS" "MATA"                              8342.7      55.76          8950.82     84.96            0                0
 30 "VEICULOS" "TOMATE"                                 0          0                0         0        66900            200.6
 31 "VEICULOS" "TOMATE"                                 0          0                0         0            0                0
 32 "VEICULOS" "GALIA"                               2000          0             2000     91.84            0                0
 33 "VEICULOS" "GABARITO"                               0          0                0         0            0                0
 34 "VEICULOS" "MATA"                                2500          0              500         0            0                0
 35 "VEICULOS" "GALIA"                                  0          0                0         0            0                0
 36 "VEICULOS" "MATA"                                   0          0                0         0         9000                0
 37 "VEICULOS" "TOMATE"                                 0          0                0         0        26500            129.2
 38 "VEICULOS" "MATA"                                   0          0                0         0        15000                0
 39 "VEICULOS" "CASTELO"                                0          0                0         0      1719.49                0
 40 "VEICULOS" "GABARITO"                               0          0                0         0            0                0
 41 "VEICULOS" "MATA"                                   0          0                0         0            0                0
 42 "VEICULOS" "JERONIMO MONTEIRO"                      0          0                0         0            0                0
 43 "VEICULOS" "IUNA"                                   0          0                0         0            0                0
 44 "VEICULOS" "RIO"                                    0          0                0         0            0                0
 45 "VEICULOS" "MATA"                                   0          0                0         0            0                0
 46 "VEICULOS" "GALIA"                                  0          0                0         0            0                0
 47 "VEICULOS" "TOMATE"                                 0          0                0         0            0                0
 48 "VEICULOS" "GABARITO"                               0          0                0         0            0                0
 49 "VEICULOS" "TOMATE"                       36270243.93 2499024.78      69927348.97 982860.51 139823383.96       2133616.24
 50 "VEICULOS" "GALIA"                        79199672.03   33379.91 91442922.1400001  32398.71  99044951.08       1955510.53
 51 "VEICULOS" "GALIA"                        40256959.75    1947.84      40637036.55   5242.71  64180953.64        945622.43
 52 "VEICULOS" "GABARITO"                     56481852.68    13188.3      56105617.99  16760.07  61467938.52       1164673.43
 53 "VEICULOS" "MATA"                         28227341.05     141.38      30397580.33     22.87  54807917.93        153541.02
 54 "VEICULOS" "VIANA"                         7503199.01  306873.45      15204214.68 453458.88  53058326.07       1747509.94
 55 "VEICULOS" "GALIA"                        12207840.41    5988.21      11738571.85   5178.41  51410770.05        933845.43
 56 "VEICULOS" "CABRA"                         6234068.45     324.44      10303670.33    114.61  44126375.91         53189.82
 57 "VEICULOS" "CABRA"                        21321269.36    10268.4      23085009.03   4403.57  40292533.87       1033701.18
 58 "VEICULOS" "MATA"                         25347361.82    9430.33      26796727.42   7834.77   39595203.9         90885.68
 59 "VEICULOS" "MATA"                          37834456.5          0      67997224.89         0  36859472.24                0
 60 "VEICULOS" "CABRA"                         3971939.93     994.38       3105014.11   1950.73  35269189.87                0
 61 "VEICULOS" "GALIA"                        49177108.44     3075.5      41755482.61  13914.97  35073935.62        157457.86
 62 "VEICULOS" "MATA"                         29395929.44    4369.68      21643446.52   7246.95  34869477.48        879118.61
 63 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"      16523362.43    5106.09      16098753.03   8834.22  33556600.77        746286.84
 64 "VEICULOS" "GABARITO"                     19730699.01    3131.14      25276917.96   1285.69  32834894.16 81177.3700000001
 65 "VEICULOS" "GALIA"                        24992842.34    6986.03      26045192.37  16226.48  30263601.09         66141.87
 66 "VEICULOS" "GALIA"                        26505195.77    8184.27      25719094.13  14901.45  26909556.11         60607.36
 67 "VEICULOS" "GABARITO"                     22921225.95   15805.08      16681291.29  12047.32  25136335.93        581936.77
 68 "VEICULOS" "GABARITO"                     10976119.68          0       5272851.73   4723.81  24351619.38         69041.61
 69 "VEICULOS" "MATA"                         21793461.58    7145.22      21123027.49   8724.02   22040447.6        100088.45
 70 "VEICULOS" "CABRA"                         3089946.79     431.39       2902018.28    349.39  21572576.25         55690.74
 71 "VEICULOS" "ROMA"                         10741864.95    1812.09      11018529.77    3671.4  21364365.17        431222.12
 72 "VEICULOS" "TOMATE"                        6330086.16     509.64       6608193.43    417.87  20767201.62        112578.19
 73 "VEICULOS" "GALIA"                                  0          0                0         0  19821327.44        345478.35
 74 "VEICULOS" "ROMA"                         10645811.51    5437.15      10115628.79  20681.32  19334625.18         75616.23
 75 "VEICULOS" "ROMA"                          9520449.29     2070.1       6068971.92   6406.94  19262529.99        328732.44
 76 "VEICULOS" "GABARITO"                      9783409.42    2335.81       8828413.53   3500.97  18799004.03         29372.99
 77 "VEICULOS" "CABRA"                          3993076.2   90842.05        5443790.1   2928.53  18734259.31           385231
 78 "VEICULOS" "GALIA"                        32075359.46    2376.79      39136760.08   6845.56  18121559.78         49191.44
 79 "VEICULOS" "MATA"                          8513524.74    1074.11       7912980.48   4009.34  17530720.85         95684.67
 80 "VEICULOS" "GALIA"                         9735425.28    1179.26       9261581.91         0  17237798.06         57485.03
 81 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       9761198.56    4520.34       6422876.68   5327.63  16723890.68         30380.09
 82 "VEICULOS" "GALIA"                        12178331.94     899.57      11265011.25    406.24  16528948.81          40726.4
 83 "VEICULOS" "GALIA"                        12161583.23      84.34       8503122.14    133.15  14971406.18         13885.02
 84 "VEICULOS" "GABARITO"                      5907479.18   20014.72       6636078.65  27129.43  14326268.03         61730.95
 85 "VEICULOS" "CABRA"                         4587921.79    4611.62       5332246.66   2999.05  14118387.61         67885.32
 86 "VEICULOS" "GALIA"                        17878251.51    7978.31      18290305.43   5730.71  14099024.64         39790.46
 87 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       8260480.72    16887.7       8676673.11    622.81   13947755.6        182718.32
 88 "VEICULOS" "CABRA"                   8993436.55000001    3710.43       6969507.33   4612.46  13923164.14                0
 89 "VEICULOS" "GALIA"                          8615907.5     682.74       7408275.52     589.6  13219782.87           252.91
 90 "VEICULOS" "MATA"                         18194895.04     3763.8      13621800.81   3595.49  12776045.45        204405.56
 91 "VEICULOS" "MATA"                          4772730.35    2952.94       4096693.34    132.22  12606368.12         66014.67
 92 "VEICULOS" "GABARITO"                     14576640.84   13914.32      10185506.15  13996.58  12338430.94          90071.7
 93 "VEICULOS" "SAO MATEUS"                    7011158.43     967.76       4662489.89   3065.71  10970791.04          4887.02
 94 "VEICULOS" "ROMA"                          5604126.25    1084.69        5891984.6   1137.33  10673795.58         11669.25
 95 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       5586772.71     149.76       3874129.96      8.08  10050033.55        290624.83
 96 "VEICULOS" "CACHOEIRO DE ITAPEMIRIM"       2504687.41    1734.81       3693058.93  11182.55   9726815.61                0
 97 "VEICULOS" "CABRA"                         9864328.82       7148      11417812.71   6080.46   9467304.22         38895.85
 98 "VEICULOS" "CABRA"                         5195855.39    2137.39       5535605.43   3768.05   9366446.47         16157.73
 99 "VEICULOS" "VENDA NOVA DO IMIGRANTE"       2465742.24      94.99       2552504.47       245    8711207.1         84562.71
100 "VEICULOS" "GALIA"                         5675943.51    7339.68       6047564.62   3116.77   8504805.93         45185.19
end

cls


ds *_jan19 // get a list of our predictors with the minimal stub

local stubs `r(varlist)'
//set these as a macro
di "`stubs'"


local stubs: subinstr local stubs "_jan19" "", all

/* Now that we've got our varaibles we'll be reshaping,
we then strip them of their underscores and reshape them */

di "`stubs'"

greshape long `stubs', i(id) j(month) string

// note the string is necessary
//since the result (month) will be a string, _[month]##

replace month = subinstr(month, "_", "", .)

g int year = 2000 + real(substr(month, -2, .))

//extract the two year suffix (last 2 characters) from month
//and make it a more recent year

replace month = substr(month, 1, 3)

br

Announcement

Reshaping With Multiple Stubs and Years

Comment

Comment