Creating number of obs and summing for new variable value

[tom harbell]

Hello,
I have data on loans, one loan per observation, each with a zipcode and amount attached as variables .

1.I looking to get the number of observations/loans per zipcode (string) or zipnumber (same value but long).
So I'm looking to have 1 distinct zipcode as an observation and a number of loans for that zip code as a new variable value.

2. I looking to sum the "amount" variable for each zipcode into a new variable.
So I'm looking to get distinct zipcodes for each observation with an amount summed from the "amount" variable.

Thanks,
Tom

[Clyde Schechter]

Please read the Forum FAQ for excellent advice about the most effective ways to give information about your problem. In particular, there you will find that attachments are discouraged overall, and that giving example data in any format other than a Stata data set is likely to be altogether unhelpful. The useful way to show example data is with the -dataex- command. 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- to 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.

That said, assuming that the amount variable is properly structured as a numeric variable in your Stata data set, you can get the results you are asking for with:

collapse (count) N = zipnumber (sum) amount, by(zipcode)

Do read -help collapse- to learn the many other kinds of aggregated data that -collapse- can produce.