generate a panel data based on country and year

[Fimi Karimi]

Hello,
I have a dataset based on each ID company as an identifier per each year. It include IDcompany Director Year sex Boardsize Country
Now I have to change my data based on each countries per year:
Unitedstate Firm A 1990
Unitedstate Firm B 1990
Unitedstate Firm A 1991
Unitedstate Firm B 1991
Canada FirmC 1990
Canada Firm D 1991

Could you please help me how I can change Identifier to Country from ID company? I mean how I can create this panel data?

[Andrew Musau]

If you are moving from firm-level data to country-level data, you need some form of aggregation. Either taking the mean of variables, summing the variables, some combination of these or something else. See

help collapse

The code will look something like:

collapse (mean) age (sum) earnings (firstnm) capital_city, by(country year)

[Fimi Karimi]

Thank you Andrew. Could you please tell me how I can create a firm-level data based on countries, firm and year?
Sorry, I am new and I am not very good at Stata. My data include all firms and their characteristics and their head office for each year . If I want to change my data based on each country (I don't want to aggregate them) , would it be some form of "sort by"?
now it is like this:
Company year country Age
Firm A 1990 unitedstate 40
Firm B 1990 Canada 50
Firm C 1990 Canada 60
Firm D 1990 United state 50
Firm G 1990 China 43
Firm E 1990 France 45
Firm A 1991 unitedstate 40
Firm B 1991 Canada 50
Firm C 1991 Canada 60
Firm D 1991 United state 50
Firm G 1991 China 43
Firm E 1991 France 45

I want to change ma data based on countries in firm level for each year
Country Company year Age
Unitedstate Firm A 1990 40
Canada Firm B 1990 50
Canada Firm C 1990 60
United state Firm D 1990 50
China Firm G 1990 43
France Firm E 1990 45
Unitedstate Firm A 1991 40
Canada Firm B 1991 50
Canada Firm C 1991 60
United state Firm D 1991 50
China Firm G 1991 43
France Firm E 1991 45

[Andrew Musau]

You have firm-level data. In case firms can share the same name across countries:

egen firm= group(Company country), label
xtset firm year

Then you can include country as a variable in your analysis. For a panel dataset, you need at most one observation per cross-sectional unit and time period.

[Fimi Karimi]

Hello Andrew,
I hope you are fine. I am using tobit regression for my test. there is high correlation between some of my variables and I want to test multicollinearity. But when I run VIF after tobit regression it gives me this error:
tobit BGD Race GDP_GR GDP_PCapita FD Common GenderQuota FE_Laborforce MAS UAI FE_Parliament i.Year, ll(0) ul(100)

vif
not appropriate after regress, nocons;
use option uncentered to get uncentered VIFs


even I uses this one but it gave an error again:
estat vif, uncentered
estat vif not valid


Could I ask you help me with that? How I can test multicollinearity with tobit?
Thanks

[Fimi Karimi]

dataex CountryCode Year BGD Religion GDP_GR GDP_PCapita FD Common FE_Education FE_Laborforce WageEquality FE_Parliament MAS UAI

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

* Example generated by -dataex-. For more info, type help dataex
clear
input str4 CountryCode int Year double(BGD Religion GDP_GR) float GDP_PCapita double FD byte Common double(FE_Education FE_Laborforce WageEquality FE_Parliament) int(MAS UAI)
"ARE" 2005                  .                  .  4.85514119630904 10.649952 .39039862 1                . 13.1184458867102                 .                0 52 68
"SAU" 2007                  .                  .  1.84713025491795  9.664989 .47904012 1 33.4199104309082 13.9921732987244                 .                0 52 68
"SAU" 2011                  .                  .  10.9937616282548  10.01867  .4526957 1 33.4334716796875 15.1836468305014               .65                0 52 68
"SAU" 2005                  .                  .  5.57385012169572  9.507683 .33075544 1 33.4182891845703 13.5669740000449                 .                0 52 68
"QAT" 2008                  .                  .  17.6635563626089 11.287424 .53604543 0 34.2301483154297 11.0595843929554                 .                0  .  .
"QAT" 2010                  .  11.79245283018868  19.5923315337859 11.198506 .58593041 0 34.2414093017578 11.9103972685831               .64                0  .  .
"QAT" 2007                  .                  .  17.9856568160263 11.077624 .50695515 0 34.2234992980957  12.135712898169                 .                0  .  .
"PNG" 2018  33.29999923706055                  . -.279252284959952  7.857221 .17640525 .                . 47.5900009239479                 .                0  .  .
"PNG" 2019  33.29999923706055                  .  4.48043062171888   7.86087 .17952736 .                . 47.6285444498989                 .                0  .  .
"ARE" 2003                  .                  .  8.80054081486432 10.392294 .28656438 1                . 12.8290340361508                 .                0 52 68
"PNG" 2021 42.900001525878906                  . .0999999999999943  7.880251   .183662 .                . 47.8421270245407                 .                0  .  .
"KWT" 2003                  .                  .  17.3260204175143 10.033688  .2963073 0 42.0934982299805 26.0715677672416                 .                0 52 68
"QAT" 2016 1.7000000476837158  11.79245283018868  3.06419188430156 10.976222 .53122711 0 34.2536392211914 13.5109920462666              .794                0  .  .
"SAU" 2003                  .  40.41278295605859   11.242061385412  9.140111 .36042747 1 33.4012794494629 12.5399958253738                 .                0 52 68
"QAT" 2011                  .  11.79245283018868  13.3751764107928  11.44028 .52156526 0 34.2438507080078 12.8888749530599               .69                0  .  .
"QAT" 2012                  .  11.79245283018868  4.73001184360784 11.493145 .57913768 0 34.2455101013184 13.4045840844708               .72                0  .  .
"QAT" 2013                  .  11.79245283018868  5.55604064306606  11.48895 .43163359 0 34.2458686828613 13.6119065983778               .77                0  .  .
"SAU" 2012                  .                  .  5.42739426611275  10.08869 .46860802 1 33.4339218139648  15.340261089684               .62                0 52 68
"SAU" 2008                  .                  .  6.24977275215682  9.849288 .46582031 1 33.4248390197754 13.9119095993111                 .                0 52 68
"ARE" 2004                  .                  .  9.56643663716161 10.519153 .34354287 1                . 13.0180264496439                 .                0 52 68
"SAU" 2009                  .                  . -2.05924918904591  9.620105 .48711842 1 33.4263305664063 13.7818620480922               .51                0 52 68
"SAU" 2006                  .                  .   2.7884022241319  9.605661 .41952425 1 33.4183502197266 14.1608224560957                 .                0 52 68
"PNG" 2017 22.200000762939453                  .  3.53461081750458    7.8221 .17652313 .                . 47.5402586900592                 .                0  .  .
"KWT" 2004                  .                  .  10.2402980586353 10.225622 .45130968 0 42.0938606262207 25.8913184441675                 .                0 52 68
"SAU" 2004                  .  40.41278295605859  7.95844166576318  9.299726  .3317889 1 33.4066200256348 13.0084440015649                 .                0 52 68
"QAT" 2009                  .                  .  11.9565611289085 11.014258 .55865484 0 34.2392807006836 10.9475883893405               .65                0  .  .
"QAT" 2015  .8999999761581421  11.79245283018868  4.75334572438737 11.112223     .4779 0 34.2506217956543  13.418423961145               .79                0  .  .
"PNG" 2020               37.5                  . -3.16738117629638  7.802244 .17974038 .                . 47.6791433657019                 .                0  .  .
"QAT" 2006                  .                  .  26.1702456703031 11.001747 .56329191 0 34.2186584472656 13.7093909925041                 .                0  .  .
"QAT" 2014                  .  11.79245283018868  5.33432329196459  11.44171 .48955974 0 34.2490005493164 13.5408254352528               .81                0  .  .
"SAU" 2010                  .                  .  5.03949288842378  9.795844 .42193806 1 33.4330787658691  14.495430544227                .6                0 52 68
"PNG" 2003                  .                  .  2.16410250222082  6.364059 .13713887 .                . 47.2277943439169                 . .917431192660551  .  .
"PNG" 2005                  .                  .  6.34479592309654  6.618385 .16854991 .                . 47.2654850043661                 . .917431192660551  .  .
"PNG" 2009                  .                  .  6.80042148322095  7.364527 .22868429 .                . 47.3575078586686                 . .917431192660551  .  .
"PNG" 2006                  .                  .  5.40994409107307  7.127272 .16359928 .                . 47.2797458917826                 . .917431192660551  .  .
"PNG" 2010                  .                  .  10.1284540720231  7.538623  .2090247 .                .  47.404206328681                 . .917431192660551  .  .
"PNG" 2011                  .                  .  1.10754362619616  7.742327 .20417577 .                . 47.4299145745343                 . .917431192660551  .  .
"PNG" 2008                  .                  . -.296457845880581  7.399422 .17752774 .                . 47.3191926961052                 . .917431192660551  .  .
"PNG" 2004                  .                  .  2.72117574098234  6.436207 .14758304 .                . 47.2468678997645                 . .917431192660551  .  .
"PNG" 2007                  .                  .  7.81518922862387  7.229206 .17850086 .                . 47.2943099373258                 . .917431192660551  .  .
"KWT" 2005                  .                  .  10.6090449840228 10.495294 .48407108 0                . 26.0023312970085                 . 1.53846153846154 52 68
"KWT" 2015                  .                  .   .59301961722123  10.28586 .47213978 0 42.1043891906738 26.1779923848679               .67 1.53846153846154 52 68
"KWT" 2021 3.5999999046325684                  .  1.30805650896014  10.37931 .39950451 0                . 24.5971216648278              .676 1.53846153846154 52 68
"KWT" 2006                  .                  .  7.51477300024021 10.668273 .45531407 0                . 26.4807820740161                 . 1.53846153846154 52 68
"KWT" 2014                  .                  .  .500876982158658 10.674517 .45572245 0  42.101261138916 28.0468988568818               .63 1.53846153846154 52 68
"KWT" 2007                  .                  .  5.99157551059315 10.730506 .46656907 0                .  27.032003405441                 . 1.53846153846154 52 68
"PNG" 2022                  .                  .  4.59999997896274  8.013102         . .                . 47.9029911219383                 . 1.73913043478261  .  .
"EGY" 2009  7.599999904632568                  .  4.67359979952375  7.701751 .40068603 0      49.76171875 23.7525000216262               .81 1.76211453744493 52 68
"EGY" 2008                  .                  .  7.15628356605793  7.571422  .4341726 0  49.760871887207  22.862823264303                 . 1.80995475113122 52 68
"EGY" 2007                  .                  .  7.08782742681057  7.369268 .37269419 0 49.7489814758301 24.1797294490625                 . 1.80995475113122 52 68
"EGY" 2011                  7                  .  1.76457194899826  7.880661 .27634493 0 49.7804718017578 22.9040376999174               .85 1.96850393700787 52 68
"EGY" 2012  5.700000047683716                  .  2.22619979863505 8.0258875 .26931337 0 49.7920684814453 23.0061116393418               .82 1.96850393700787 52 68
"EGY" 2005                  .                  .  4.47174447174454  7.032717 .42101404 0 49.7425308227539 22.1064269975913                 . 1.98237885462555 52 68
"KWT" 2016                  .                  .  2.92586822591576 10.204578 .50367785 0 42.1130981445313 25.2314293962335              .647                2 52 68
"EGY" 2006                  .                  .  6.84383819379115  7.194692 .36965203 0  49.743091583252 22.4991604559105                 . 2.03619909502262 52 68
"EGY" 2003                  .                  .   3.1934547373977   6.96313 .32502744 0 49.7204284667969 21.3623924222306                 . 2.42290748898678 52 68
"PNG" 2015                  .                  .  6.57835632304275  7.824875 .17956716 .                . 47.4614447541782                 .  2.7027027027027  .  .
"PNG" 2016 11.100000381469727                  .  5.48957315278187  7.754771 .18608253 .                . 47.4986427232164                 .  2.7027027027027  .  .
"PNG" 2012                  .                  .  4.65711959686238  7.883481 .20839587 .                . 47.4388126939716                 .  2.7027027027027  .  .
"PNG" 2013                  .                  .  3.82494632800674  7.854963 .17863734 .                .  47.460441101907                 .  2.7027027027027  .  .
"PNG" 2014                  .                  .  13.5437706216545  7.916534 .18241934 .                . 47.4361702627045                 .  2.7027027027027  .  .
"EGY" 2004                  .                  .  4.09207161125304  6.923879 .34178036 0 49.7216186523438 21.7280722704411                 . 2.86343612334802 52 68
"KWT" 2008                  .                  .  2.47975694278544 10.925856 .42683074 0                . 27.5536826228305                 . 3.07692307692308 52 68
"KWT" 2018                  .                  .    2.433981268502  10.37387  .5029611 0 42.1425704956055 24.4888191975989              .635 3.07692307692308 52 68
"KWT" 2017                  .                  . -4.71210620851997 10.283918 .51141673 0 42.1147003173828 25.2249478229033               .63 3.07692307692308 52 68
"TUR" 2003                  . 30.020582181711262  5.76320606654714  8.456332 .36754015 0 30.4205493927002 27.5183058029932                 . 4.36363636363636 45 85
"TUR" 2004                  . 30.020582181711262  9.79593638920282  8.704799 .37049574 0 30.4221706390381 25.0379703413122                 . 4.36363636363636 45 85
"TUR" 2006                  8 31.575037147102524  6.94798808571992  8.987673 .42703235 0 30.4290008544922 25.2109136786063                 . 4.36363636363636 45 85
"TUR" 2005                  7 31.575037147102524  8.99230493626517  8.905096 .42253661 0 30.4274501800537 24.9378228176727                 . 4.36363636363636 45 85
"QAT" 2022                  .                  .  4.82768910127822 11.385618         . 0                . 16.0683246990652              .747 4.44444444444444  .  .
"QAT" 2021                  .                  .  1.59099816403853 11.110032 .52963257 0 34.2715492248535 15.8664381376189              .733 4.44444444444444  .  .
"KWT" 2019                  .                  . -.551959331708445 10.330917 .36143395 0 42.1440391540527 24.2717732014407 .6365000000000001 4.61538461538462 52 68
"THA" 2017 11.800000190734863 25.533333333333335  4.17768103210009  8.769785 .69954336 1 31.0311698913574 45.5325413940201              .763              4.8 34 64
"ARE" 2006                  .                  .  9.83731977348489 10.721936 .42455798 1                .  13.106505837993                 .                5 52 68
"THA" 2018 12.699999809265137 25.533333333333335   4.2228702874607  8.871304 .72010154 1 31.0371608734131 45.5762803407096              .749 5.28455284552846 34 64
"KOR" 2003                  .                  .  3.14729119373409  9.593755 .77972126 0 42.3030281066895 40.2223170620033                 . 5.86080586080586 39 85
"THA" 2014  9.699999809265137  32.58333333333333  .984468863619426  8.669464 .70897162 1 31.0236492156982 45.4816787570096               .81 6.09137055837564 34 64
"THA" 2015                  9  29.05833333333333  3.13404724911635  8.649763 .71393299 1                . 45.5846614805896               .77 6.09137055837564 34 64
"THA" 2016 12.199999809265137  29.05833333333333  3.43515771692182   8.67496 .73656207 1 31.0242900848389 45.5866454368377              .771 6.09137055837564 34 64
"KWT" 2013                  .                  .   1.1493004370652    10.774 .43267649 0                . 28.5339229098479               .66 6.15384615384615 52 68
"KWT" 2012                  .                  .  6.62581830014173 10.844884 .42983717 0                . 28.6148352505257                .7 6.15384615384615 52 68
"KWT" 2022 2.9000000953674316                  .  8.17775584614963  10.67437         . 0                . 24.9941628203269              .668             6.25 52 68
"KWT" 2020                  .                  . -8.85527888535746 10.098137 .40609518 0  42.145378112793 24.4053128701243              .638 6.34920634920635 52 68
"JPN" 2003                  .                  .  1.53512549928012   10.4741 .74422985 0                . 40.8229704880194                 . 7.08333333333333 95 92
"JPN" 2004                  .                  .   2.1861156944216  10.55318 .75945032 0                . 41.0825712028092                 . 7.08333333333333 95 92
"RUS" 2003                  .                  .  7.29995234453868  7.998041  .5009883 0 33.7796096801758  49.021066379108                 . 7.57238307349666 36 95
"KWT" 2011                  .                  .   9.6284069747757 10.799787 .43807715 0                .  28.723332497077               .67 7.69230769230769 52 68
"KWT" 2009                  .                  . -7.07605643073411  10.54287 .44860989 0                .  28.058406384641               .68 7.69230769230769 52 68
"KWT" 2010                  .                  . -2.37026412020438 10.576755 .45848098 0                . 28.5538987362916               .66 7.69230769230769 52 68
"MLT" 2003                  .                  .  4.07456985210095  9.530409 .47253478 . 38.9049491882324 30.5609044040003                 . 7.69230769230769 47 96
"JPN" 2012  1.100000023841858  32.62382316823577  1.37475099920874 10.802536 .81143719 0                . 42.0841485623639                .6 7.91666666666667 95 92
"IDN" 2003                  .               37.8  4.78036912167654  6.958841 .27034214 0 44.5190200805664 36.4675749774904                 .                8 46 48
"JPN" 2013  1.100000023841858  32.62382316823577   2.0051001767726 10.618853 .84051132 0 43.0172882080078 42.5440599860952               .62            8.125 95 92
"JPN" 2014 1.7000000476837158  32.62382316823577  .296205514142627 10.557775 .84337604 0 43.0263710021973 42.7878385864757               .68            8.125 95 92
"IND" 2004                  .  40.40959040959041  7.92293661286503  6.436319 .44653493 1 44.7780113220215 26.5787302072952                 . 8.25688073394496 56 40
"IND" 2006                  .              43.95  8.06073257303272  6.687126 .47088125 1 44.8244895935059 26.5393673336259                 . 8.25688073394496 56 40
"IND" 2005                  .              43.95  7.92343062149763  6.565982 .45476121 1 44.8085899353027 26.7842718107762                 . 8.28729281767956 56 40
"COL" 2008                  .  8.132231404958677   3.2834461861654  8.618791 .34031686 0 52.7511901855469 39.8373979669038                 . 8.43373493975904 64 80
"COL" 2007                  .  8.132231404958677  6.73819469090975   8.46844 .29943684 0 52.7472496032715 39.6116968115852                 . 8.43373493975904 64 80
"COL" 2009 11.399999618530273  8.132231404958677  1.13964864548061  8.565212 .35577878 0 52.7558288574219 40.8700351436891               .59 8.43373493975904 64 80
end

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

Listed 100 out of 1553 observations



I run this command:
tobit BGD Relligion GDP_GR GDP_PCapita FD Common FE_Laborforce WageEquality FE_Education FE_Parliament i.Year, ll(0) ul(100)

[Andrew Musau]

Multicollinearity is a property of the data, not the model. Therefore, just run OLS and compute the VIFs.

quietly regress ...
estat vif

[Fimi Karimi]

Thanks for your help

[Fimi Karimi]

Hello Andrew, hope you are fine. I would appreciate it if you could tell me when and at which percentile we should winsorize our data? It's the statistics of my data. Thanks for your time.

[Andrew Musau]

I would appreciate it if you could tell me when and at which percentile we should winsorize our data? It's the statistics of my data.

We don't generally do this in economics, but I see it done in finance. Your variables appear to be economic variables. If so, I would recommend not doing it. Otherwise, ask someone in your field if it is not economics or look at previous studies.

[Fimi Karimi]

Thank you for your advice. Yes, my data is economic. I saw in some finance studies that they winsorize financial and accounting varibles. Thanks.

[Fimi Karimi]

My variables, Religious _Percentage and Nonreligious_Perc is based on the survey respondant's answer. In some countries I have a 100% religious_Perc which is not reasonable. Do you have any suggestion for dealing with them? I think they make my results biased.

[Andrew Musau]

Ask that in a new thread, providing all the background information.