Dear All,

I'm trying to compute a ratio based on a date (def_date) in the dataset. I try to give a basic working example to explain the problem. Thank you for the help.

Code:
clear
input float(def_date def date_start date_end)
195 1 180 252
195 1 181 270
196 1 170 260 
197 0 165 222 
198 1 159 223
201 0 198 224
202 0 187 225
203 0 199 225
205 1 199 219
205 1 150 240
. 0 140 219
. 0 199 219
. 0 201 219
. 1 205 218
. 0 187 217
. 1 177 216
end

format %tq def_date date_start date_end
gen id_loan = _n
order id
browse



gen active = 0
replace active = 1 if date_start <= date_end

bysort def_date : gen def_count_q = sum(active)

bysort def_date : egen def_count_max_q = max(def_count_q) if def_date != .

twoway (line def_count_max_q def_date), title(Number of default per quarter of default)

* for each def_date
* goal: compute for each def_date number of def=1 loans and divide it by total number of active loans in that def_date
* problem: def_date is available only for loans with def=1 status
* solution:
* numerator: number of def=1 loans for each def_date, e.g. in 2008 = 2 (done! see variable def_count_max_q) 
* denominator = calculate number of loans active in that particular def_date, meaning that date_start < def_date < date_end
*    -> e.g. in 2008q4 I have active id_loan 3,4,5,6,7,8,9,10,11,15,16 = 11 loans
*     -> e.g. in 2011q2 I have active id_loan 11,12,13,14,15,16 = 7
* last operation: compute per each def_date the def_rate = n.default / tot.active loans : 2/11 in 2008q4

* I was thinking to solve it with a loop for each def_date
qui: levelsof def_date, local(levels)
foreach l of local levels{
di %tq `l'
bysort `l': count if date_start < `l' & `l' > date_end
count if date_start < `l'
}