Create a variable if a condition is met evey year

[Antonio Stanco]

Hello all
My dataset look like this

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 ISO int year long bank_id double pc_npl long(npleur grossloan)
"IE" 2006 1   .93   148  15916
"IE" 2007 1  1.48   262  17700
"IE" 2008 1  3.68   692  18821
"IE" 2009 1  7.83  1427  18215
"IE" 2010 1 14.18  2482  17503
"IE" 2011 1     .     .      .
"IE" 2012 1 27.72  4448  16046
"IE" 2013 1 47.95  7304  15234
"IE" 2014 1  51.5  7544  14648
"IE" 2015 1 47.37  6674  14088
"IE" 2016 1 43.23  5728  13249
"IE" 2017 1 35.04  4286  12232
"IE" 2018 1     .     .      .
"IE" 2019 1     .     .      .
"IT" 2006 2     .     .      .
"IT" 2007 2     .     .      .
"IT" 2008 2     .     .      .
"IT" 2009 2     .     .      .
"IT" 2010 2     .     .      .
"IT" 2011 2  2.37    33   1381
"IT" 2012 2  3.36    46   1372
"IT" 2013 2  6.65    84   1270
"IT" 2014 2  9.85   120   1215
"IT" 2015 2 18.47   217   1173
"IT" 2016 2 16.42   186   1135
"IT" 2017 2 15.49   184   1191
"IT" 2018 2  8.75   108   1234
"IT" 2019 2     .     .      .
"NL" 2006 3  1.32  6299 477112
"NL" 2007 3   .94  5219 554982
"NL" 2008 3  1.38  8592 622376
"NL" 2009 3  2.05 11983 583410
"NL" 2010 3  2.23 13779 618495
"NL" 2011 3   2.2 13382 607592
"NL" 2012 3  2.62 14928 568992
"NL" 2013 3  2.96 15921 537878
"NL" 2014 3  3.23 16889 523467
"NL" 2015 3  2.17 15325 706579
"NL" 2016 3  2.39 13597 568838
"NL" 2017 3  2.16 12481 579050
"NL" 2018 3  1.86 11102 596687
"NL" 2019 3     .     .      .
"IT" 2006 4     .     .      .
"IT" 2007 4     .     .      .
"IT" 2008 4     .     .      .
"IT" 2009 4     .     .      .
"IT" 2010 4     .     .      .
"IT" 2011 4  3.55   239   6717
"IT" 2012 4  4.72   321   6807
"IT" 2013 4  6.12   435   7108
"IT" 2014 4  7.54   747   9916
"IT" 2015 4 14.68  1469  10008
"IT" 2016 4 14.86  1509  10155
"IT" 2017 4 13.15  1463  11125
"IT" 2018 4  5.61   695  12389
"IT" 2019 4     .     .      .
"IT" 2006 5     .     .      .
"IT" 2007 5     .     .      .
"IT" 2008 5     .     .      .
"IT" 2009 5  7.87   441   5600
"IT" 2010 5  7.34   454   6186
"IT" 2011 5  8.79   559   6364
"IT" 2012 5 10.44   709   6793
"IT" 2013 5 12.45   849   6819
"IT" 2014 5  16.3  1784  10945
"IT" 2015 5 27.75  3046  10975
"IT" 2016 5 26.69  2832  10608
"IT" 2017 5 24.92  2556  10256
"IT" 2018 5 15.73  2009  12772
"IT" 2019 5     .     .      .
"IT" 2006 6     .     .      .
"IT" 2007 6     .     .      .
"IT" 2008 6     .     .      .
"IT" 2009 6     .     .      .
"IT" 2010 6     .     .      .
"IT" 2011 6  3.34   796  23851
"IT" 2012 6  4.63  1190  25689
"IT" 2013 6  6.62  1668  25186
"IT" 2014 6  7.99  2010  25145
"IT" 2015 6 16.79  4265  25342
"IT" 2016 6  16.6  4427  26658
"IT" 2017 6 15.76  4225  26814
"IT" 2018 6 12.58  4145  32960
"IT" 2019 6     .     .      .
"IT" 2006 7     .     .      .
"IT" 2007 7     .     .      .
"IT" 2008 7     .     .      .
"IT" 2009 7     .     .      .
"IT" 2010 7     .     .      .
"IT" 2011 7  11.7  1290  11024
"IT" 2012 7 13.73  1476  10747
"IT" 2013 7 15.58  1598  10253
"IT" 2014 7 17.39  1596   9179
"IT" 2015 7 26.07  2368   9081
"IT" 2016 7 26.05  2232   8570
"IT" 2017 7 24.65  2152   8727
"IT" 2018 7 13.22  1101   8331
"IT" 2019 7     .     .      .
"IT" 2006 8     .     .      .
"IT" 2007 8     .     .      .
end
format %ty year
label values bank_id bank_id
label def bank_id 1 "15927", modify
label def bank_id 2 "21126", modify
label def bank_id 3 "29199", modify
label def bank_id 4 "29469", modify
label def bank_id 5 "29501", modify
label def bank_id 6 "29503", modify
label def bank_id 7 "29504", modify
label def bank_id 8 "29979", modify

I want to create, for each bank and for each year, a dummy variable that takes value 1 if the BANK percentage of NPL (pc_npl) is greater than the percentage of NPL at ISO(country level).
Can ayone help me?

[Clyde Schechter]

Where in your data set is "the percentage of NPL at ISO (country level)?" Or, how would it be calculated from the variables you show?

[Antonio Stanco]

Hello, thank for your reply. I my dataset both the "npleur" and "grossloan" are at individual bank level, so the percentage of NPL at ISO level must be calculated, if it is possible

[Clyde Schechter]

I'm still not sure I understand. Do you mean that we have to total the variable npleur over all observations for a year, and also total it within each country for a year, and then take the ratio? If so, that looks like this:

by year, sort: egen year_total_npl = total(npleur)
by ISO year, sort: egen country_year_total_npl = total(npleur)
gen country_pct_npl = 100*country_year_total_npl/year_total_npl

If that's right, then your original thread topic suggests you want a variable showing whether a bank's npl percentage exceeds that of its country in every year. If that means what I think it means, then

gen bank_exceeds_country_this_year = pc_npl > country_pct_npl if !missing(pc_npl)
by bank_id, sort: egen bank_always_exceeds_country = min(bank_exceeds_country_this_year)

[Antonio Stanco]

Thank you very much for your suggestion.
And yes, I think that you perfectly explain what I meant.
I need a variable, year_total_npl, which is the sum of all bank's npl (npleur) within a country, for every year. The same for grossloan.
In this way, I guess, I have a picture of a country banking system. Then, I would take the ratio of these two variable to get the percentage of npl at country level.
In the end, I would create a variable that take the value 1 if the pc_npl (bank level) is greater than the percentage of npl at country level.

[Antonio Stanco]

by ISO year, sort: egen country_year_total_npl = total(npleur)

. by ISO year, sort: egen country_year_total_loan = total(grossloan)

. g country_year_pc_npl=(country_year_total_npl/country_year_total_loan)*100

g highnpl= 1 if pc_npl>country_year_pc_npl
replace highnpl=0 if highnpl==. | pc_npl==.

The last condition, | pc_npl==. is added since I have noticed that if pc_npl is missing and country_year_pc_npl have some values, I have a 1 in the highnpl dummy.