How to deflate uneven panel data

[Serkan Kocabas]

Hi everyone,

I am working with firm level dataset for Spain around 87,000 firms (long format in stata), I have unevenly 10 years observations for each firm such as some firms have observations from 30/12/1994 to 30/12/2004 and some has 30/12/2011 to 30/12/2021, total year observation goes from 1994 to 2021. I want to deflate all the variables for each year. I have annual GDP deflator from World Bank (1994 to 2021). My question is how to combine two dataset and match the year observations (I have DDMMYYYY format for the firm level dataset and YYYY format for the GDP deflator) and deflate the variables.

Best regards,

[William Lisowski]

In your firm-level dataset, create a year variable from your date variable, and use it to merge the dataset containing the CDP deflators.

With that said, descriptions of data are well-meant but insufficient to help those who want to help you. Even the best descriptions of data are no substitute for an actual example of the data. There are many ways your data might be organized that are consistent with your description, and each would require a somewhat different approach. In order to get a more helpful response that what I just wrote, you need to show some example data from both of your datasets.

Be sure to use the dataex command to do this. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, dataex is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it. dataex will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Serkan Kocabas]

Thank you for the information William. This is an example of my data. I also have GDP deflator dataset which only contains two columns, year and gap deflator.

I hope this helps.

Best,

* Example generated by -dataex-. To install: ssc install dataex
clear
input str101 name int year double(ta tfa) long ne double(cf s) int patents str72 nace int age
"1"                     18992   854576   41963  7   -66192   495380 . "F - Construction"                                                         22
"1"                     19358   843158   43573  7   -46205   602203 . "F - Construction"                                                         22
"1"                     19723   888696   43573  9  -143062   456898 . "F - Construction"                                                         22
"1"                     20088   934791   43573  8  -193094   449722 . "F - Construction"                                                         22
"1"                     20453   963021   63557  7    17188   700461 . "F - Construction"                                                         22
"1"                     20819   793664   63557  7    37155   633588 . "F - Construction"                                                         22
"1"                     21184   625274   75274  7    93369   707007 . "F - Construction"                                                         22
"1"                     21549   477380   84846  7    91835   750569 . "F - Construction"                                                         22
"1"                     21914   363689  119277  8   100308   793100 . "F - Construction"                                                         22
"1"                     22280   351835  121766 14   111954   812842 . "F - Construction"                                                         22
"2"                          18992   528349  300636  5    22206   610299 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"2"                          19358   524390  309470  5     3704   595710 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"2"                          19723   518887  306637  5     4175   595206 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"2"                          20088   530598  306983  5     4896   633981 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"2"                          20453   511553  304150  5     5396   737991 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"2"                          20819   509234  295493  5    11575   800966 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"2"                          21184   487728  288002  5    12910   852772 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"2"                          21549   476936  281646  5    11600   826509 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"2"                          21914   485253  275290  5    16176   847497 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"3"                                19174  7289657 2503120 41   516541  9083297 . "M - Professional, scientific and technical activities"                    28
"3"                                19539  8808224 2165498 48   630311 12235975 . "M - Professional, scientific and technical activities"                    28
"3"                                19904  9342112 1548766 48   537281 10818658 . "M - Professional, scientific and technical activities"                    28
"3"                                20088 11125000 1416411 48   251723  5282197 . "M - Professional, scientific and technical activities"                    28
"3"                                20453 17336817 3129180 49   797778 12420022 . "M - Professional, scientific and technical activities"                    28
"3"                                20819 16950125 3647011 45   589749  9196874 . "M - Professional, scientific and technical activities"                    28
"3"                                21184 20366943 4729188 44   600207  8228352 . "M - Professional, scientific and technical activities"                    28
"3"                                21549 26996622 5180659 39  2618172  8045621 . "M - Professional, scientific and technical activities"                    28
"3"                                21914 35775406 8960776 22  3963282  8016165 . "M - Professional, scientific and technical activities"                    28
"3"                                22280 38071650 9500502 11 11757117  4723662 . "M - Professional, scientific and technical activities"                    28
"4"                          18992   887842  408016  5    15188   626228 . "N - Administrative and support service activities"                        36
"4"                          19358   899957  408016  5    44749   626364 . "N - Administrative and support service activities"                        36
"4"                          19723  1137376  408016  5    66621   791702 . "N - Administrative and support service activities"                        36
"4"                          20088  1006575  404516  5    55446   537131 . "N - Administrative and support service activities"                        36
"4"                          20453   996586  420592  5    31235   647938 . "N - Administrative and support service activities"                        36
"4"                          20819  1213609  420592  4   142865   886524 . "N - Administrative and support service activities"                        36
"4"                          21184  1211925  425873  5   143008   851472 . "N - Administrative and support service activities"                        36
"4"                          21549  1156147  427761  5     4380   798572 . "N - Administrative and support service activities"                        36
"4"                          21914  1005248  429143  6  -122894   515372 . "N - Administrative and support service activities"                        36
"4"                          22280   813272  429143  2   -80283   261409 . "N - Administrative and support service activities"                        36
"5" 18992   352414   26342 10    16023   503295 . "M - Professional, scientific and technical activities"                    34
"5" 19358   363437   21280  7    14092   497022 . "M - Professional, scientific and technical activities"                    34
"5" 19723   344333   23406  7    12036   432882 . "M - Professional, scientific and technical activities"                    34
"5" 20088   349232   18798  6    10286   443232 . "M - Professional, scientific and technical activities"                    34
"5" 20453   339926    8637  7    10103   444359 . "M - Professional, scientific and technical activities"                    34
"5" 20819   336210   19958  5    12512   456007 . "M - Professional, scientific and technical activities"                    34
"5" 21184   363079   18191  6    13704   485622 . "M - Professional, scientific and technical activities"                    34
"5" 21549   349173   16730  6    14942   528508 . "M - Professional, scientific and technical activities"                    34
"5" 21914   337830   16482  7     8304   530648 . "M - Professional, scientific and technical activities"                    34
"6"                    18992   248926   72565  6     3861   603175 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    19358   224498   60215  7    16427   556603 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    19723   183753   62883  5    15213   497330 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    20088   159187   43401  5    17861   480241 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    20453   154958   36593  5    12158   456065 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    20819   180902   30647  5    23471   482775 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    21184   167041   24576  6    17856   456363 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    21549   169407   20864  6    14536   431656 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    21914   163703   16617  6    15015   451035 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"6"                    22280   134321   12930  6   -42255   306420 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 25
"7"                                 18992   813519  590712  8    76277   731458 . "C - Manufacturing"                                                        23
"7"                                 19358   757786  564059  7    39507   467506 . "C - Manufacturing"                                                        23
"7"                                 19723   755587  540808  8    21820   480754 . "C - Manufacturing"                                                        23
"7"                                 20088   494647  259989  6   -60959   578484 . "C - Manufacturing"                                                        23
"7"                                 20453   429721  253813  9    28374   607770 . "C - Manufacturing"                                                        23
"7"                                 20819   395311  246150  8    49988   579931 . "C - Manufacturing"                                                        23
"7"                                 21184   350867  238192  9    30942   514602 . "C - Manufacturing"                                                        23
"7"                                 21549   616056  532632 10    17542   430206 . "C - Manufacturing"                                                        23
"7"                                 21914   555003  437600 10    37065   428282 . "C - Manufacturing"                                                        23
"8"                             18992   549860  404217  5    22805   591643 . "F - Construction"                                                         22
"8"                             19358   577748  382352  6    22750   608968 . "F - Construction"                                                         22
"8"                             19723   544644  361592  6    22145   467767 . "F - Construction"                                                         22
"8"                             20088   577866  365504  6    44404   818033 . "F - Construction"                                                         22
"8"                             20453   729581  338888  6    11965   554116 . "F - Construction"                                                         22
"8"                             20819   682952  346581  6    36171   987060 . "F - Construction"                                                         22
"8"                             21184   777943  439823  7    53707   985501 . "F - Construction"                                                         22
"8"                             21549   663449  429158  8    14022   886035 . "F - Construction"                                                         22
"8"                             21914   671353  406665  8    34501  1038834 . "F - Construction"                                                         22
"9"                      18992   923034  258514 16     -995   646817 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      19358   785442  247375 17    25758   919269 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      19723   925987  302777 19    23124  1010006 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      20088  1261066  336141 17    35945  1324309 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      20453  1051671  483024 18    49607  1223844 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      20819  1058775  458917 17    82885  1106190 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      21184  1062153  430899 22    83033  1298125 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      21549  1144486  418037 26   -43700  2863518 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      21914   857550  273890 19   112741  2945445 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"9"                      22280  1203932  259265 15    11234  1413794 . "G - Wholesale and retail trade; repair of motor vehicles and motorcycles" 22
"10"                    18992    68377   30148  2     6727   279382 . "I - Accommodation and food service activities"                            13
"10"                    19358    98064   26579  3    25424   307957 . "I - Accommodation and food service activities"                            13
"10"                    19723    92393   31172  4    21782   335874 . "I - Accommodation and food service activities"                            13
"10"                    20088    84601   30771  4    -8805   390515 . "I - Accommodation and food service activities"                            13
"10"                    20453    80042   26599  4   -14553   374221 . "I - Accommodation and food service activities"                            13
"10"                    20819    52182   20697  3   -24502   323782 . "I - Accommodation and food service activities"                            13
"10"                    21184    41231   14820  1      919   341222 . "I - Accommodation and food service activities"                            13
"10"                    21549    34616    7197  4    43447   385860 . "I - Accommodation and food service activities"                            13
"10"                    21914   127514    2134  2    62455   448623 . "I - Accommodation and food service activities"                            13
"10"                    22280   125760    2877  3    37442   317820 . "I - Accommodation and food service activities"                            13
"11"                          18992   369800    4734  6    47097   495337 . "I - Accommodation and food service activities"                            11
"11"                          19358   346327    5665  8    37331   501052 . "I - Accommodation and food service activities"                            11
"11"                          19723   339668   20416  7    44975   457284 . "I - Accommodation and food service activities"                            11
"11"                          20088   280225   23999  7    52677   480970 . "I - Accommodation and food service activities"                            11
end
format %td year

[William Lisowski]

In post #2 I wrote

In order to get a more helpful response than what I just wrote, you need to show some example data from both of your datasets.

You're halfway there, with example data from one of the two datasets.

My advice in post #2 was

In your firm-level dataset, create a year variable from your date variable, and use it to merge the dataset containing the CDP deflators.

With what you've shown, you can create the year variable with the following code

. rename year dec31year

. generate year = yofd(dec31year)

. list dec31year year if name=="1", clean

       dec31year   year  
  1.   31dec2011   2011  
  2.   31dec2012   2012  
  3.   31dec2013   2013  
  4.   31dec2014   2014  
  5.   31dec2015   2015  
  6.   31dec2016   2016  
  7.   31dec2017   2017  
  8.   31dec2018   2018  
  9.   31dec2019   2019  
 10.   31dec2020   2020

Now both this dataset and your world bank dataset have variables named year. Presumably

merge m:1 year using worldbankdata

would achieve what you want, although I cannot be sure since you've not provided an example of your World Bank data as was requested.

But this should be enough of a start that you can find your way to the end with no further help.