Hi all,

I have a database of projects that belong to a particular category and have a launch date and a deadline. For each project, I'd like to compute how many projects in the same category have been launched 24h after the deadline. Project duration is not constant. I tried to run the code shown below, but I'm not getting what I need. I'm guessing it has to do with the sorting and the fact that campaign duration is heterogeneous.

Code:
sort category launched_at
gen campaigns_launched_24 = 0
local more = 1
local i = 0
while `more' {
    local i = `i' + 1
    by category: gen doit = (launched_at[_n + `i']  - deadline) <= msofhours(24)
    replace campaigns_launched_24 = campaigns_launched_24 + doit
    count if doit
    local more = r(N)
    drop doit
}
Here's a sample of my data:

Code:
clear
input str11 category long project_id double(launched_at deadline)
"Art" 1578671837 1556799741000 1559389080000
"Art"  353710709 1557766931000 1.5620832e+12
"Art"  199916122 1557783598000 1558646760000
"Art"  995325523 1557853227000 1564105380000
"Art"  725084811 1559851618000 1567454940000
"Art"  446705094 1560400631000  1.563174e+12
"Art" 1036167768 1562718749000 1.5653058e+12
"Art"  541268297 1563030134000 1567396740000
"Art" 1097561326 1563825605000 1571500740000
"Art" 1358511195 1564508515000 1.5659277e+12
"Art" 1262817514 1564860621000 1569383940000
"Art" 1331047419 1564966004000 1570247940000
"Art" 1303299859 1565114928000 1572824160000
"Art"  242607068 1565313089000 1568769360000
"Art"  654539635 1565744244000 1569211140000
"Art"  994711737 1565898541000 1569957720000
"Art" 2077234434 1566769666000 1570136460000
"Art"  546326967 1567046149000 1569988740000
"Art"  600922270 1567066462000 1.5712632e+12
"Art" 1704281050 1567303359000  1.572642e+12
"Art" 1054364934 1567343405000 1.5712416e+12
"Art" 1613913308 1567435152000  1.572642e+12
"Art"  611017496 1567612308000 1573919040000
"Art"  518209887 1567910303000  1.575306e+12
"Art"  662389409 1568149343000 1.5758424e+12
"Art"  149233916 1568234691000 1574132820000
"Art" 2098335561 1568449452000  1.575738e+12
"Art"  558687555 1568483157000 1.5762132e+12
"Art" 1982126934 1568486739000 1576181820000
"Art" 1686482513 1568499141000 1.5712416e+12
"Art"  574627229 1568522713000 1572407940000
"Art" 1748013708 1568560802000 1572757140000
"Art" 1896639590 1568569063000 1.5711609e+12
"Art" 1048291575 1568583712000 1.5763545e+12
"Art"  255262442 1.5685866e+12 1576358580000
"Art" 2063543996 1568588410000 1.5754248e+12
"Art"  134305743 1568597970000 1576289220000
"Art"  604752938 1568604717000 1574571540000
"Art"  475787700 1568604896000 1.5762564e+12
"Art"  578708004 1568605178000 1576299540000
"Art"     637867 1568633765000 1576348080000
"Art"  357969158 1568637301000 1576396260000
"Art"  295233939 1568670496000 1576363020000
"Art"  745788330 1568679605000   1.56987e+12
"Art" 1486569394 1568741077000 1573019940000
"Art"  440417064 1568862128000 1572929940000
"Art" 1159543817 1568914135000 1575659340000
"Art" 1325701851 1569605291000 1.5773364e+12
"Art" 1921156210 1.5696193e+12 1.5773925e+12
"Art" 1117882630 1569639019000 1576617420000
end
format %tc launched_at
format %tc deadline
Thank you all very much for your help.