Add values of a variable based on different criteria

[Ioannis pragidis]

Hi,

I have a panel data with panelid representing clients, contract different types of contracts,billtype indicating the type of bill a consumer pays to an energy utility. Start and End date indicate the billing period and the InvoicedAmount indicates the amount payed by a client.

Within the billing period of each Clearance-type bill there are several or sometimes none In advance payments. I would like, for each Clearance-type bill and for each different contract , to add all payments (Indicated by the "InvoicedAmount") done by a client.

In the example below (for the first four rows), this means to add 148.89+60.5+44.16

* Example generated by -dataex-. For more info, type help dataex
clear
input str61 contract float panelid str43 billtype int(startDate endDate) double InvoicedAmount
"Home" 17 "Clearance"  22497 22596 148.89
"Home" 17 "In advance" 22497 22538   60.5
"Home" 17 "In advance" 22538 22568  44.16
"Home" 17 "In advance" 22568 22599   45.7
"Κ.Ο.Τ.B"      17 "Clearance"  22596 22620  51.51
"Κ.Ο.Τ.B"      17 "In advance" 22620 22691 -37.07
"Κ.Ο.Τ.B"      17 "In advance" 22691 22719 -12.15
"Κ.Ο.Τ.A"      18 "Clearance"  22235 22246  48.43
"Κ.Ο.Τ.A"      18 "Clearance"  22246 22370  -4.18
"Κ.Ο.Τ.A"      18 "In advance" 22246 22295  52.73
"Κ.Ο.Τ.A"      18 "In advance" 22295 22326  33.36
"Κ.Ο.Τ.A"      18 "In advance" 22326 22354  25.76
end
format %tdnn/dd/CCYY startDate
format %tdnn/dd/CCYY endDate

I would appreciate any advice to help me solve this.

Best,

Ioannis

[Øyvind Snilsberg]

gen clearance_enddate = endDate if billtype == "Clearance"
by panelid contract (clearance_enddate), sort: gen InvoicedAmount2 = InvoicedAmount if endDate <= clearance_enddate[1]
by panelid contract: egen wanted = total(InvoicedAmount2)

[Rich Goldstein]

here is one way (you will need to install the -spell- command from SSC):

gen byte clear=billtype=="Clearance"
spell contract, fcond(clear==1)
egen wanted=total(InvoicedAmount), by( _spell)

this puts the amount in each observation of a "spell" and you may want it only once - you can select on clear==1 or _seq==1 or _end==1 to get that

added: crossed with #2 which does not need a user-written command

[Ioannis pragidis]

Thank you both very much!