Calculating differences between the highest and lowest value for consecutive years in panel data

[Itesh New]

Hello Stata Users, for a panel data, how do we calculate differences in inflation rates between the region with the lowest and the region with the highest average inflation rate for consecutive years?

 * Example generated by -dataex-. To install: ssc install    dataex clear input long id str23 state float(date inf_yoy_adj) 1 "Andaman"                 636  6.047025 1 "Andaman"                 637  6.319858 1 "Andaman"                 638  6.308668 1 "Andaman"                 639  5.340158 1 "Andaman"                 640  5.754745 1 "Andaman"                 641   7.05944 1 "Andaman"                 642  8.182131 1 "Andaman"                 643  8.983887 1 "Andaman"                 644  6.636281 1 "Andaman"                 645  6.631428 1 "Andaman"                 646  6.498218 1 "Andaman"                 647  6.072977 1 "Andaman"                 648  5.955406 2 "Andhra_pradesh"          636 10.494335 2 "Andhra_pradesh"          637  11.08665 2 "Andhra_pradesh"          638  9.817925 2 "Andhra_pradesh"          639  9.403549 2 "Andhra_pradesh"          640 10.289693 2 "Andhra_pradesh"          641  10.80626 2 "Andhra_pradesh"          642 10.933035 2 "Andhra_pradesh"          643   11.7187 2 "Andhra_pradesh"          644 12.079797 2 "Andhra_pradesh"          645  11.30015 2 "Andhra_pradesh"          646  11.10584 2 "Andhra_pradesh"          647   9.24498 2 "Andhra_pradesh"          648  8.042706 3 "Arunachal_pradesh"       636 12.524086 3 "Arunachal_pradesh"       637  13.30902 3 "Arunachal_pradesh"       638 13.188683 3 "Arunachal_pradesh"       639 12.103922 3 "Arunachal_pradesh"       640 10.946158 3 "Arunachal_pradesh"       641 10.289434 3 "Arunachal_pradesh"       642  10.97117 3 "Arunachal_pradesh"       643  10.71902 3 "Arunachal_pradesh"       644 10.098755 3 "Arunachal_pradesh"       645  9.186599 3 "Arunachal_pradesh"       646  9.855748 3 "Arunachal_pradesh"       647  8.105375 3 "Arunachal_pradesh"       648  7.629341 4 "Assam"                   636  9.646822 4 "Assam"                   637  9.352273 4 "Assam"                   638  9.220056 4 "Assam"                   639  6.881263 4 "Assam"                   640  6.192063 4 "Assam"                   641  7.579785 4 "Assam"                   642  7.526994 4 "Assam"                   643  8.654333 4 "Assam"                   644  9.748801 4 "Assam"                   645  9.883399 4 "Assam"                   646 11.646427 4 "Assam"                   647    10.478 4 "Assam"                   648 10.133062 5 "Bihar"                   636 11.795957 5 "Bihar"                   637 12.645008 5 "Bihar"                   638 12.057347 5 "Bihar"                   639 9.4219265 5 "Bihar"                   640  8.813066 5 "Bihar"                   641  9.387949 5 "Bihar"                   642 10.206976 5 "Bihar"                   643 10.774814 5 "Bihar"                   644    12.908 5 "Bihar"                   645   13.8658 5 "Bihar"                   646 16.101423 5 "Bihar"                   647 12.751816 5 "Bihar"                   648 10.558828 6 "Chandigarh"              636   8.29232 6 "Chandigarh"              637  8.232577 6 "Chandigarh"              638  7.789394 6 "Chandigarh"              639  6.283062 6 "Chandigarh"              640  4.940287 6 "Chandigarh"              641  5.436759 6 "Chandigarh"              642  6.545688 6 "Chandigarh"              643  6.869637 6 "Chandigarh"              644  7.429382 6 "Chandigarh"              645  8.280512 6 "Chandigarh"              646 9.1845255 6 "Chandigarh"              647  6.661587 6 "Chandigarh"              648   7.24603 7 "Chhattisgarh"            636  9.637038 7 "Chhattisgarh"            637  10.21423 7 "Chhattisgarh"            638  9.807084 7 "Chhattisgarh"            639  8.451326 7 "Chhattisgarh"            640   7.66571 7 "Chhattisgarh"            641   9.35176 7 "Chhattisgarh"            642  9.581611 7 "Chhattisgarh"            643 9.3486805 7 "Chhattisgarh"            644 11.931405 7 "Chhattisgarh"            645  13.82851 7 "Chhattisgarh"            646 15.339169 7 "Chhattisgarh"            647  12.17591 7 "Chhattisgarh"            648 11.169917 8 "Dadra_ Nagar_ Daman_Diu" 636  9.532771 8 "Dadra_ Nagar_ Daman_Diu" 637  9.555807 8 "Dadra_ Nagar_ Daman_Diu" 638  9.321163 8 "Dadra_ Nagar_ Daman_Diu" 639  9.651157 8 "Dadra_ Nagar_ Daman_Diu" 640  9.751176 8 "Dadra_ Nagar_ Daman_Diu" 641  9.906502 8 "Dadra_ Nagar_ Daman_Diu" 642 11.973926 8 "Dadra_ Nagar_ Daman_Diu" 643 13.304586 8 "Dadra_ Nagar_ Daman_Diu" 644 12.696772 end format %tm date label values id state label def state 1 "Andaman", modify label def state 2 "Andhra_pradesh", modify label def state 3 "Arunachal_pradesh", modify label def state 4 "Assam", modify label def state 5 "Bihar", modify label def state 6 "Chandigarh", modify label def state 7 "Chhattisgarh", modify label def state 8 "Dadra_ Nagar_ Daman_Diu", modify

[Nick Cox]

That didn't work very well. Without editing the whole of what turned out to one line this gives some flavour.

* Example generated by -dataex-. To install: ssc install  dataex 
clear 
input long id str23 state float(date inf_yoy_adj) 
1 "Andaman"                 636  6.047025 
1 "Andaman"                 637  6.319858 
1 "Andaman"                 638  6.308668 
1 "Andaman"                 639  5.340158 
1 "Andaman"                 640  5.754745 
1 "Andaman"                 641   7.05944 
1 "Andaman"                 642  8.182131 
1 "Andaman"                 643  8.983887 
1 "Andaman"                 644  6.636281 
1 "Andaman"                 645  6.631428 
1 "Andaman"                 646  6.498218 
1 "Andaman"                 647  6.072977 
1 "Andaman"                 648  5.955406 
2 "Andhra_pradesh"          636 10.494335 
2 "Andhra_pradesh"          637  11.08665 
end

This needs some work on our behalf to make your goals clear.

These look like monthly dates, so what is a year? Let's guess Western calendar year. If you have a different definition, you'll need different code.

gen year = year(dofm(date))

Region presumably means state or union territory.

Mean inflation rate should perhaps be geometric mean inflation rate, although what about any deflation?

So, a sketch is

egen region_rate = mean(log(inf)), by(state year) 
egen lowest = min(region_rate), by(year) 
egen highest = max(region_rate), by(year) 
gen wanted = exp(highest) - exp(lowest)

If you're content with arithmetic mean rates, then

egen region_rate = mean(inf), by(state year) 
egen lowest = min(region_rate), by(year) 
egen highest = max(region_rate), by(year) 
gen wanted = highest - lowest

[Itesh New]

Thanks Nick Cox for the solution and sorry about the confusion in pasting the data.