Summing up a particular variable of duplicate county and then dropping the duplicate to make panel

[Tariq Abdullah]

Hello, I've data from 2000-2010. For every year I have some duplicate counties in my current data setup. I'd like to add the total trade variable for a single county ( whcih has duplicates ) for each year, and after adding the variable up , I'd like to drop the duplicates , so that only the highest value of total trade remains with the rest of my non-duplicate data.

Like for county 6037 I'd like to add 48127534 and 69850483 ( therefore I can have the total value of trade in that county in that particular year) , and then drop all the duplicates. So that my information remains intact, and at the same time I have panel data.

How I'd be able to do it ?

* Example generated by -dataex-. For more info, type help dataex
clear
input int year long(county1 totaltrade)
2000  1097  53658350
2000  6001  12161363
2000  6013  19387971
2000  6037  48127534
2000  6037  69850483
2000  6077   2051086
2000  6111   1193742
2000  9009  10603972
2000 10003   5174066
2000 10003   8535482
2001 42101   3988882
2001 42101  46372067
2001 44007   9029829
end

[Clyde Schechter]

by county1 year (totaltrade), sort: gen long sum_of_totaltrade = sum(totaltrade)
by county1 year (totaltrade): keep if _n == _N

Note: The variable totaltrade contains 8 digit numbers. When you add just two of those, a carry could bring this to 9 digits. That will still fit in a long. But if you have situations where you have many observations for a given county and year, the total could spill up into the 10 digits. In that case, long does not have enough bits to keep that precision. So if that situation can arise in your data, you should change -long- to -double- in order to retain precision.

[Tariq Abdullah]

Thanks so much, Mr. Schechter for this wonderful coding trick, and appreciate the pointer you kindly gave me!