Adding up deposits over year and customer from monthly data

[Andy Meyer]

Hi there

This is how my dataset looks. There are 1,845,832 observations in it.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(yearmonth businesspartner) float netnewmoney
201201 1234    0
201202 1234  150
201203 1234  240
201204 1234    0
201205 1234  120
201206 1234  240
201207 1234  120
201208 1234  120
201209 1234    0
201210 1234  160
201211 1234  390
201212 1234  160
201301 1234    0
201302 1234  160
201303 1234  200
201304 1234    0
201305 1234  400
201306 1234    0
201307 1234  400
201308 1234  500
201309 1234    0
201310 1234  200
201311 1234  400
201312 1234  200
201401 1234  200
201402 1234  200
201403 1234    0
201404 1234  200
201405 1234  400
201406 1234    0
201407 1234  400
201408 1234    0
201409 1234  200
201410 1234  400
201411 1234    0
201412 1234  400
201501 1234  200
201502 1234  200
201503 1234    0
201504 1234  200
201505 1234  200
201506 1234  200
201507 1234  400
201508 1234    0
201509 1234  200
201510 1234  400
201511 1234    0
201512 1234  400
201601 1234    0
201602 1234  200
201603 1234  200
201604 1234  400
201605 1234    0
201606 1234    0
201607 1234  400
201608 1234    0
201609 1234  400
201610 1234    0
201611 1234  300
201612 1234  600
201701 1234    0
201702 1234  300
201703 1234  200
201704 1234    0
201705 1234  100
201706 1234  100
201707 1234  200
201708 1234    0
201709 1234  400
201710 1234    0
201711 1234  200
201712 1234  400
201801 1234    0
201802 1234  400
201803 1234  700
201804 1234    0
201805 1234  300
201806 1234  600
201201 5678    0
201202 5678    0
201203 5678 3000
201204 5678    0
201205 5678 2000
201206 5678    0
201207 5678    0
201208 5678    0
201209 5678    0
201210 5678    0
201211 5678    0
201212 5678    0
201301 5678    0
201302 5678    0
201303 5678    0
201304 5678    0
201305 5678    0
201306 5678    0
201307 5678    0
201308 5678    0
201309 5678    0
201310 5678    0
end

As you can see it is monthly data of new deposits by customers.

I would now like to generate a variable that gives me the annual deposits. So a sum of these monthly deposits for each year and each customer.

Thanks for the help

[Nick Cox]

Anyone minded to reply might first read #2 in https://www.statalist.org/forums/for...lysing-dataset

[Andy Meyer]

What is the problem here?

Anyone minded to reply might first read #2 in https://www.statalist.org/forums/for...lysing-dataset

[Rich Goldstein]

first you need a year variable, then sum within year and businesspartner (assuming that businesspartner is what you mean by "customer"; if not, please clarify)

gen year=substr(string(yearmonth),1,4)
egen double anndepo=total(netnewmoney), by(year businesspartner)

you probably want some way to get just one answer per year per businesspartner (that's a guess since you don't say anything about the use of this new variable); is so, use the "tag" function of the -egen- command

[William Lisowski]

In response to post #3, perhaps Nick was trying to suggest disappointment that you apparently have overlooked the advice in section 16 of the Statalist FAQ (linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create yours post) on providing feedback to those who take the time to address your questions.

Trying to wrap up a thread you started is helpful, especially if you report what solved your problem. You can then thank those who tried to help. Conversely, ignoring answers is less sociable, even if those answers did not solve your problem. "Thanks in advance" does not absolve you from either expectation.

[Andy Meyer]

Thank you very much mister Goldstein

Yes I only want one answer per year and businesspartner. I intend to see the total deposits per year in total, the total deposits per year and customer and the average deposit per year over all customers.

[Andy Meyer]

first you need a year variable, then sum within year and businesspartner (assuming that businesspartner is what you mean by "customer"; if not, please clarify)

gen year=substr(string(yearmonth),1,4)
egen double anndepo=total(netnewmoney), by(year businesspartner)

you probably want some way to get just one answer per year per businesspartner (that's a guess since you don't say anything about the use of this new variable); is so, use the "tag" function of the -egen- command

Thanks for that, it really helped me. Now I would like to do the same but totalling multiple variables.
I tried it like this but I got answers like 7 or 8 Dollars for a whole year. (it should be way more)

egen double anndepoTypeA = total(netnewmoneyGroup1 & netnewmoneyGroup2 & netnewmoneyGroup3), by(year businesspartner)

I assume this is not how this works and would be glad if you told me how it's done.

Thanks

[Rich Goldstein]

this refers to, apparently, a number of variables in your data set but that are not shown in your -dataex- extract; here is a guess: you need first to sum within each observation (use the -egen- command with the "rowtotal" function) and then you can use something like your code in #7 to sum within across observations

[Nick Cox]

A bug in #7 can be found by careful consideration of

. display 42 & 666
1

. display 42 + 666
708

. help operators

https://www.stata.com/support/faqs/d...rue-and-false/