How to average a variable before and after a key event

[Giulia Gaggioni]

Dear All,

Could you please help me with this problem: how to average the sleep variable t_NREM 5 days before and 5 days after the immunological variable IL1bf (which occurs only occasionally)? There are missing data.
I have something like this (for simplicity, I report a data subset of just one id)

* Example generated by -dataex-. For more info, type help dataex
clear
input str21 id float(date1 t_NREM) double IL1bf
"10E" 21566 294                 .
"10E" 21567 185                 .
"10E" 21568  53                 .
"10E" 21569 249                 .
"10E" 21571  40                 .
"10E" 21573  42                 .
"10E" 21575  91                 .
"10E" 21576 367                 .
"10E" 21577 408                 .
"10E" 21578 167 43.84374999999999
"10E" 21581 161                 .
"10E" 21582  75                 .
"10E" 21584  86                 .
"10E" 21585 204                 .
"10E" 21586  79                 .
"10E" 21587 141                 .
"10E" 21589 201                 .
"10E" 21590  42                 .
"10E" 21591 212                 .
"10E" 21594 181                 .
"10E" 21596 104                 .
"10E" 21597  13                 .
"10E" 21599 149                 .
"10E" 21601 113                 .
"10E" 21602 103                 .
"10E" 21603 151                 .
"10E" 21604  60                 .
"10E" 21606 117                 .
"10E" 21607   .                 .
"10E" 21608 105                 .
"10E" 21610 113                 .
"10E" 21611  88                 .
"10E" 21616 149                 .
"10E" 21617  21                 .
"10E" 21618  97                 .
"10E" 21620 269                 .
"10E" 21621 121                 .
"10E" 21622 210                 .
"10E" 21623   . 80.00000000000001
"10E" 21624  64                 .
"10E" 21625 243                 .
"10E" 21634  74                 .
"10E" 21635  31                 .
"10E" 21636  86                 .
"10E" 21737 188                 .
"10E" 21738 322                 .
"10E" 21739 260                 .
"10E" 21740 154                 .
"10E" 21741  79                 .
"10E" 21742 136                 .
"10E" 21743 126                 .
"10E" 21744 173                 .
"10E" 21748   . 44.11764705882352
"10E" 21759 107                 .
"10E" 21760 144                 .
end
format %td date1

and would aim for something like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input str21 id float(date1 t_NREM) double IL1bf float(dpre dpost)
"10E" 21578 167 43.84374999999999    227   118
"10E" 21623   . 80.00000000000001 174.25 153.5
"10E" 21748   . 44.11764705882352  149.5     .
end
format %td date1

Many thanks in advance for your feedback.

[Andrew Musau]

See rangestat from SSC.

ssc install rangestat, replace

For a 5 day window including the current observation, you want :

rangestat (mean) pre= t_NREM, interval(date1 -4 0) by(id)
rangestat (mean) post= t_NREM, interval(date1 0 4) by(id)

Change the highlighted intervals for something different.

Res.:

keep if !missing(IL1bf)
(52 observations deleted)

. l

     +-----------------------------------------------------------+
     |  id       date1   t_NREM       IL1bf      pre        post |
     |-----------------------------------------------------------|
  1. | 10E   29jan2019      167    43.84375   258.25   134.33333 |
  2. | 10E   15mar2019        .          80      200       153.5 |
  3. | 10E   18jul2019        .   44.117647      173           . |
     +-----------------------------------------------------------+

[Giulia Gaggioni]

Thank you so much @Andrew Musau for the clear explanation - very helpful!