Creating an inflation index series

[Falco Wolf]

Hello,

From the data below, you can see that i have the inflation for a few years. However, this came from some data that went far behind 2007. So I want to create an inflation series that starts at year 1.
Then I want to create the real_cmat dividing c_mat / inflation.

I started by trying to use this
gen index = 1
replace index = index[_n-1] * (1 + inflation) if _n > 1, but this gives me a accumulative and its not the inflation.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 Country_Code int year str52 Country_Name double inflation str9 c_mat
"ITA" 2007 "Italy"     1.829741122024 "615945"
"ITA" 2008 "Italy"   3.34783258401023 "579282"
"ITA" 2009 "Italy"   .774768131387384 "478557"
"ITA" 2010 "Italy"   1.52551602118248 "540085"
"ITA" 2011 "Italy"   2.78063272879324 "513460"
"ITA" 2012 "Italy"   3.04136333226773 "432065"
"ITA" 2013 "Italy"   1.21999342274305 "396066"
"ITA" 2014 "Italy"   .241047429826773 "425068"
"ITA" 2015 "Italy"  .0387903996579552 "357058"
"ITA" 2016 "Italy" -.0940166569157271 "372849"
"ITA" 2017 "Italy"   1.22653316645808 ""      
end

[Clyde Schechter]

You do not explain what your inflation variable actually is. Looking at the numbers, I'm going to assume that it represents year of year increase in prices expressed as a percentage. If so, your approach to creating an index was almost right.

After that, you have a problem because your c_mat variable is a string. So you need to make it numeric before you adjust it. And the adjustment, if I grasp your intention correctly, would involve dividing by the index, not by the current inflation percentage.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 Country_Code int year str52 Country_Name double inflation str9 c_mat
"ITA" 2007 "Italy"     1.829741122024 "615945"
"ITA" 2008 "Italy"   3.34783258401023 "579282"
"ITA" 2009 "Italy"   .774768131387384 "478557"
"ITA" 2010 "Italy"   1.52551602118248 "540085"
"ITA" 2011 "Italy"   2.78063272879324 "513460"
"ITA" 2012 "Italy"   3.04136333226773 "432065"
"ITA" 2013 "Italy"   1.21999342274305 "396066"
"ITA" 2014 "Italy"   .241047429826773 "425068"
"ITA" 2015 "Italy"  .0387903996579552 "357058"
"ITA" 2016 "Italy" -.0940166569157271 "372849"
"ITA" 2017 "Italy"   1.22653316645808 ""      
end

by Country_Code (year), sort: gen index = 1 if _n == 1
by Country_Code (year): replace index = index[_n-1] * (1+inflation/100) if _n > 1

destring c_mat, replace
gen real_c_mat = c_mat/index

Note: I have also written this code so that it works separately for each country in your data set, which, I assume, is what you want.

[Nick Cox]

c_mat was numeric in this previous thread: https://www.statalist.org/forums/for...ies-in-a-panel

[Falco Wolf]

Clyde, that's actually exactly what I needed. Thanks a lot.


There is one other thing I need help with, and I feel bad spamming the front page with threads.

I'm interested in having the index variable (or even the inflation variable before the modification) merged into another dataset. The only thing they coincide in, is in years. Would it be possible to tell stata to merge only the variable index or inflation, matching it per year in all the panel?

Here is how the dataset im merging to looks like.


input float firm_id double(closdate_year turn)
1 2007 3000
1 2009 11000
2 2016 71000
3 2016 .
4 2007 1841487
4 2008 2300573
4 2009 1007198
4 2010 1263340
4 2011 1034239
4 2012 539470
5 2007 610064
5 2008 595101
5 2009 491656
5 2010 542363
5 2011 528512
5 2012 444428
5 2013 407241
5 2014 436512
5 2015 367083
5 2016 381487
6 2011 14958
6 2012 131965
6 2013 173274
6 2014 143327
6 2015 263160
6 2016 151514
7 2007 1707725
7 2008 1785326
7 2009 1444539
7 2010 1323801
7 2011 1091350
7 2012 1159131
7 2013 1031793
7 2014 534215
7 2015 1395732
7 2016 1429306
8 2007 709721
8 2008 751262
8 2009 643837
8 2010 461521
8 2011 417131
8 2012 330940
8 2013 302023
8 2014 289213
8 2015 299966
9 2011 6400
9 2012 5400
9 2013 5400
9 2015 5400
10 2009 402201
10 2010 403520
10 2011 392889
10 2012 303709
10 2013 255774
10 2014 224270
10 2015 246292
end
[/CODE]

[Clyde Schechter]

There may be a problem that makes this impossible. In your inflation data example in #1, there is a Country_Code variable, and unless you tell me otherwise, I assume that the full data set contains values for more than just one country. But there is no corresponding country identifier in the example data in #4, so there is no way for Stata to know which country's inflation rates or index from #1 to match up with any given firm in #4.

What you want is only possible if one of these conditions holds:

1. The real data for the example shown in #4 contains a Country_Code variable which can be matched with the real data in #1.

2. The real data for the example in #1 actually only contains one country, and it is the country where the firms in #4's data are located (or do business, or whatever).

3. The firms in the real data set for the example in #4 are all from a single country, you know which country that is, and that country has its inflation data available in the real data for the example in #1.

The solution will be slightly different depending on which of these 3 holds.

[Falco Wolf]

I have each datafile seperated by country. I had the "country" dropped and forgot to add it in the dataex above.

Let me restate what I want, since I gave the wrong data.

I have a dataset that contains inflation for Italy, (the first dataex below), which is the one with the code you provided. The variable that I want to "send" to the other dataset is "inflation" and "index".

The second dataex below, has a long list of companies that go from 2007 (or 2009) to 2017 (if they haven't defaulted before). So I would like to send the "inflation" and "index" variables from the first dataex below to the second dataex below, and this to be matched by it's year.

Both only have 1 country, Italy. Therefore, it should be possible? But how?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 Country_Code int year str52 Country_Name double inflation long c_mat float(index real_c_mat)
"ITA" 2007 "Italy"     1.829741122024 615945         1   615945
"ITA" 2008 "Italy"   3.34783258401023 579282 1.0334784 560516.8
"ITA" 2009 "Italy"   .774768131387384 478557 1.0414854 459494.7
"ITA" 2010 "Italy"   1.52551602118248 540085 1.0573734 510779.8
"ITA" 2011 "Italy"   2.78063272879324 513460 1.0867751 472462.1
"ITA" 2012 "Italy"   3.04136333226773 432065 1.1198279 385831.6
"ITA" 2013 "Italy"   1.21999342274305 396066 1.1334897 349421.8
"ITA" 2014 "Italy"   .241047429826773 425068  1.136222 374106.5
"ITA" 2015 "Italy"  .0387903996579552 357058 1.1366627 314128.4
"ITA" 2016 "Italy" -.0940166569157271 372849  1.135594 328329.5
"ITA" 2017 "Italy"   1.22653316645808      . 1.1495224        .
end

* Example generated by -dataex-. To install: ssc install dataex
clear
input float firm_id str16 idnr double closdate_year str22 country float(fCAPX net_worth2)
 1 "FI10290064"    2007 "ITALY"      .    .5102041
 1 "FI10290064"    2008 "ITALY"      .    .7458863
 1 "FI10290064"    2009 "ITALY"      .    .7058824
 1 "FI10290064"    2010 "ITALY"      .    .7428572
 1 "FI10290064"    2011 "ITALY"      .           .
 1 "FI10290064"    2012 "ITALY"      .           .
 1 "FI10290064"    2013 "ITALY"  60000           .
 1 "FI10290064"    2014 "ITALY"      .   .01459854
 2 "FI26827006"    2016 "ITALY"      .  -.04869565
 3 "FI27388017"    2016 "ITALY"      .           .
 4 "IT00000020362" 2007 "ITALY"  55787   .03109599
 4 "IT00000020362" 2008 "ITALY"   2984  .003802403
 4 "IT00000020362" 2009 "ITALY"  13517    .1575858
 4 "IT00000020362" 2010 "ITALY"  90705   .08933916
 4 "IT00000020362" 2011 "ITALY" -95292  -.03800638
 4 "IT00000020362" 2012 "ITALY"      .   -.4224901
 5 "IT00000210054" 2007 "ITALY"    894  -.01413546
 5 "IT00000210054" 2008 "ITALY"  -1694  -.13188255
 5 "IT00000210054" 2009 "ITALY"  -1905   .07076305
 5 "IT00000210054" 2010 "ITALY"   1120    .0993562
 5 "IT00000210054" 2011 "ITALY"    221   .03781768
 5 "IT00000210054" 2012 "ITALY"    666    .1813268
 5 "IT00000210054" 2013 "ITALY"      1    .1903993
 5 "IT00000210054" 2014 "ITALY"    484   .22894773
 5 "IT00000210054" 2015 "ITALY"      0    .2814075
 5 "IT00000210054" 2016 "ITALY"      .    .2001612
 6 "IT00000240408" 2011 "ITALY"  -6475    .7458863
 6 "IT00000240408" 2012 "ITALY"  17437    .6437877
 6 "IT00000240408" 2013 "ITALY"  21089   .28506428
 6 "IT00000240408" 2014 "ITALY"   2333    .3132189
 6 "IT00000240408" 2015 "ITALY"  13818   .11370905
 6 "IT00000240408" 2016 "ITALY"      . -.004183243
 7 "IT00000450155" 2007 "ITALY" 170597  .011092258
 7 "IT00000450155" 2008 "ITALY"  23512   .02442162
 7 "IT00000450155" 2009 "ITALY"  59240  .014760305
 7 "IT00000450155" 2010 "ITALY" 112405 -.009187857
 7 "IT00000450155" 2011 "ITALY"   7538   -.4224901
 7 "IT00000450155" 2012 "ITALY"  27924  -.05883701
 7 "IT00000450155" 2013 "ITALY"  93336    -.400514
 7 "IT00000450155" 2014 "ITALY" 383891  .016091872
 7 "IT00000450155" 2015 "ITALY"  16882  .033350505
 7 "IT00000450155" 2016 "ITALY"      .  .007844832
 8 "IT00000520288" 2007 "ITALY" 287185  .031530328
 8 "IT00000520288" 2008 "ITALY"  18643 -.020537743
 8 "IT00000520288" 2009 "ITALY"   3219 -.028155135
 8 "IT00000520288" 2010 "ITALY"  18800   .19221133
 8 "IT00000520288" 2011 "ITALY"   9024   .14787255
 8 "IT00000520288" 2012 "ITALY"     57   .01743089
 8 "IT00000520288" 2013 "ITALY"    671  .020706505
 8 "IT00000520288" 2014 "ITALY"   2195   .02291681
 8 "IT00000520288" 2015 "ITALY"   -541   .02021666
 8 "IT00000520288" 2016 "ITALY"      .   -.4224901
 9 "IT00001040286" 2007 "ITALY" 127191   .23406193
 9 "IT00001040286" 2008 "ITALY"      0    .7170907
 9 "IT00001040286" 2009 "ITALY"      0    .6925476
 9 "IT00001040286" 2010 "ITALY"      0    .7458863
 9 "IT00001040286" 2011 "ITALY"      0    .7458863
 9 "IT00001040286" 2012 "ITALY"  -1500    .6936821
 9 "IT00001040286" 2013 "ITALY"      0    .6976667
 9 "IT00001040286" 2014 "ITALY"      0   .56733847
 9 "IT00001040286" 2015 "ITALY"      .   .57760537
10 "IT00001360122" 2009 "ITALY"   3509   .15706615
10 "IT00001360122" 2010 "ITALY"   1049     .215984
10 "IT00001360122" 2011 "ITALY"    976    .3293705
10 "IT00001360122" 2012 "ITALY" 242585    .3333073
10 "IT00001360122" 2013 "ITALY"    269    .4288885
10 "IT00001360122" 2014 "ITALY"   1240    .6383812
10 "IT00001360122" 2015 "ITALY"  21490    .6924881
10 "IT00001360122" 2016 "ITALY"      .    .7038833
11 "IT00001400357" 2008 "ITALY"  37633    .2835401
end

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 Country_Code int year str52 Country_Name double inflation long c_mat float(index real_c_mat)
"ITA" 2007 "Italy"     1.829741122024 615945         1   615945
"ITA" 2008 "Italy"   3.34783258401023 579282 1.0334784 560516.8
"ITA" 2009 "Italy"   .774768131387384 478557 1.0414854 459494.7
"ITA" 2010 "Italy"   1.52551602118248 540085 1.0573734 510779.8
"ITA" 2011 "Italy"   2.78063272879324 513460 1.0867751 472462.1
"ITA" 2012 "Italy"   3.04136333226773 432065 1.1198279 385831.6
"ITA" 2013 "Italy"   1.21999342274305 396066 1.1334897 349421.8
"ITA" 2014 "Italy"   .241047429826773 425068  1.136222 374106.5
"ITA" 2015 "Italy"  .0387903996579552 357058 1.1366627 314128.4
"ITA" 2016 "Italy" -.0940166569157271 372849  1.135594 328329.5
"ITA" 2017 "Italy"   1.22653316645808      . 1.1495224        .
end
tempfile inflation
save `inflation'

* Example generated by -dataex-. To install: ssc install dataex
clear
input float firm_id str16 idnr double closdate_year str22 country float(fCAPX net_worth2)
 1 "FI10290064"    2007 "ITALY"      .    .5102041
 1 "FI10290064"    2008 "ITALY"      .    .7458863
 1 "FI10290064"    2009 "ITALY"      .    .7058824
 1 "FI10290064"    2010 "ITALY"      .    .7428572
 1 "FI10290064"    2011 "ITALY"      .           .
 1 "FI10290064"    2012 "ITALY"      .           .
 1 "FI10290064"    2013 "ITALY"  60000           .
 1 "FI10290064"    2014 "ITALY"      .   .01459854
 2 "FI26827006"    2016 "ITALY"      .  -.04869565
 3 "FI27388017"    2016 "ITALY"      .           .
 4 "IT00000020362" 2007 "ITALY"  55787   .03109599
 4 "IT00000020362" 2008 "ITALY"   2984  .003802403
 4 "IT00000020362" 2009 "ITALY"  13517    .1575858
 4 "IT00000020362" 2010 "ITALY"  90705   .08933916
 4 "IT00000020362" 2011 "ITALY" -95292  -.03800638
 4 "IT00000020362" 2012 "ITALY"      .   -.4224901
 5 "IT00000210054" 2007 "ITALY"    894  -.01413546
 5 "IT00000210054" 2008 "ITALY"  -1694  -.13188255
 5 "IT00000210054" 2009 "ITALY"  -1905   .07076305
 5 "IT00000210054" 2010 "ITALY"   1120    .0993562
 5 "IT00000210054" 2011 "ITALY"    221   .03781768
 5 "IT00000210054" 2012 "ITALY"    666    .1813268
 5 "IT00000210054" 2013 "ITALY"      1    .1903993
 5 "IT00000210054" 2014 "ITALY"    484   .22894773
 5 "IT00000210054" 2015 "ITALY"      0    .2814075
 5 "IT00000210054" 2016 "ITALY"      .    .2001612
 6 "IT00000240408" 2011 "ITALY"  -6475    .7458863
 6 "IT00000240408" 2012 "ITALY"  17437    .6437877
 6 "IT00000240408" 2013 "ITALY"  21089   .28506428
 6 "IT00000240408" 2014 "ITALY"   2333    .3132189
 6 "IT00000240408" 2015 "ITALY"  13818   .11370905
 6 "IT00000240408" 2016 "ITALY"      . -.004183243
 7 "IT00000450155" 2007 "ITALY" 170597  .011092258
 7 "IT00000450155" 2008 "ITALY"  23512   .02442162
 7 "IT00000450155" 2009 "ITALY"  59240  .014760305
 7 "IT00000450155" 2010 "ITALY" 112405 -.009187857
 7 "IT00000450155" 2011 "ITALY"   7538   -.4224901
 7 "IT00000450155" 2012 "ITALY"  27924  -.05883701
 7 "IT00000450155" 2013 "ITALY"  93336    -.400514
 7 "IT00000450155" 2014 "ITALY" 383891  .016091872
 7 "IT00000450155" 2015 "ITALY"  16882  .033350505
 7 "IT00000450155" 2016 "ITALY"      .  .007844832
 8 "IT00000520288" 2007 "ITALY" 287185  .031530328
 8 "IT00000520288" 2008 "ITALY"  18643 -.020537743
 8 "IT00000520288" 2009 "ITALY"   3219 -.028155135
 8 "IT00000520288" 2010 "ITALY"  18800   .19221133
 8 "IT00000520288" 2011 "ITALY"   9024   .14787255
 8 "IT00000520288" 2012 "ITALY"     57   .01743089
 8 "IT00000520288" 2013 "ITALY"    671  .020706505
 8 "IT00000520288" 2014 "ITALY"   2195   .02291681
 8 "IT00000520288" 2015 "ITALY"   -541   .02021666
 8 "IT00000520288" 2016 "ITALY"      .   -.4224901
 9 "IT00001040286" 2007 "ITALY" 127191   .23406193
 9 "IT00001040286" 2008 "ITALY"      0    .7170907
 9 "IT00001040286" 2009 "ITALY"      0    .6925476
 9 "IT00001040286" 2010 "ITALY"      0    .7458863
 9 "IT00001040286" 2011 "ITALY"      0    .7458863
 9 "IT00001040286" 2012 "ITALY"  -1500    .6936821
 9 "IT00001040286" 2013 "ITALY"      0    .6976667
 9 "IT00001040286" 2014 "ITALY"      0   .56733847
 9 "IT00001040286" 2015 "ITALY"      .   .57760537
10 "IT00001360122" 2009 "ITALY"   3509   .15706615
10 "IT00001360122" 2010 "ITALY"   1049     .215984
10 "IT00001360122" 2011 "ITALY"    976    .3293705
10 "IT00001360122" 2012 "ITALY" 242585    .3333073
10 "IT00001360122" 2013 "ITALY"    269    .4288885
10 "IT00001360122" 2014 "ITALY"   1240    .6383812
10 "IT00001360122" 2015 "ITALY"  21490    .6924881
10 "IT00001360122" 2016 "ITALY"      .    .7038833
11 "IT00001400357" 2008 "ITALY"  37633    .2835401
end
tempfile firm_level_data
save `firm_level_data'

use `firm_level_data', clear
rename closdate_year year
merge m:1 year using `inflation', keepusing(inflation index)

Note: In the above, I have used the -dataex- outputs to load data and store it in temporary files. In your real situation there is no need to do that, just -use- our real firm level data file, and -merge m:1 year using- your real inflation data file.