I have a large data set of medical encounters prior to a surgical date. The data is in long form with each subject (pt_key) having multiple medical encounters - each row is an encounter. I have created a dummy variable that is 1 if the visit included 'active_rehab' and occurred prior to the surgical date.

I would like to total up the number of active_rehab visits prior to surgery for each subject. Next I would like to have the value (the total active rehab visits) listed for each row w/in a pt_key. This is so I can then collapse these rows down to just one row per pt_key and still have the total for this value in all rows.

Here is the code I tried:
Code:
bysort pt_key (days_after_index): egen active_rehab_sh_tot_b4 = sum(active_rehab) if before_surg_2yr == 1
The above code works fine, but then there are missing values for all the rows that didn't include an active

So I then generated the below code to fill in the missing values. Below I create a new variable: active_rehab_sh_tot_B4 and then try to carry the last known value forward (or backward) to fill in the values. But b/c many consecutive entries for a given pt_key are missing, it won't fill in subsequent values. The only solution is to just keep running the code over and over again until all values are filled in. With over 1M rows, this doesn't seem viable. Any help is most appreciated, thank you!

Code:
generate active_rehab_sh_tot_B4 = active_rehab_sh_tot_b4, after(active_rehab_sh_tot_b4)
Code:
bysort pt_key (days_after_index): replace active_rehab_sh_tot_B4 = active_rehab_sh_tot_B4[_n+1] ///
if missing(active_rehab_sh_tot_B4)
Code:
bysort pt_key (days_after_index): replace active_rehab_sh_tot_B4 = active_rehab_sh_tot_B4[_n-1] ///
if missing(active_rehab_sh_tot_B4)
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input int(pt_key days_after_index) float before_surg_2yr byte active_rehab float(active_rehab_sh_tot_b4 active_rehab_sh_tot_B4)
0 -1213 0 0 . .
0 -1142 0 0 . .
0 -1075 0 0 . .
0 -1071 0 0 . .
0 -1044 0 0 . .
0 -1043 0 0 . .
0 -1038 0 0 . .
0 -1036 0 0 . .
0 -1028 0 0 . .
0 -1026 0 0 . .
0 -1026 0 0 . .
0  -801 0 0 . .
0  -696 1 0 . .
0  -694 1 0 . .
0  -409 1 0 . .
0  -177 1 0 . .
0  -175 1 0 . .
0  -158 1 0 . .
0  -136 1 0 . 0
0   -93 1 0 0 0
0   -92 1 0 0 0
0   -72 1 0 . 0
0   -58 1 0 . 0
0   -57 1 0 0 0
0   -23 0 0 . 0
0    -9 0 0 . 0
0     0 0 0 . 0
0     5 0 1 . 0
0     5 0 0 . 0
0    10 0 1 . 0
0    10 0 0 . 0
0    12 0 1 . 0
0    14 0 1 . 0
0    17 0 1 . 0
0    19 0 1 . 0
0    21 0 1 . 0
0    24 0 1 . 0
0    26 0 1 . 0
0    32 0 1 . 0
0    33 0 1 . 0
0    35 0 1 . 0
0    38 0 1 . 0
0    40 0 0 . 0
0    46 0 1 . 0
0    53 0 1 . 0
0    56 0 1 . 0
0    59 0 1 . 0
0    66 0 1 . 0
0    68 0 1 . 0
0    70 0 1 . 0
0    75 0 1 . 0
0    82 0 0 . 0
0    83 0 0 . 0
0    87 0 1 . 0
0    89 0 1 . 0
0    98 0 1 . 0
0   101 0 1 . 0
0   103 0 1 . 0
0   105 0 1 . 0
0   112 0 0 . 0
0   112 0 1 . 0
0   131 0 1 . 0
0   138 0 1 . 0
0   145 0 0 . 0
0   145 0 1 . 0
0   159 0 1 . 0
0   173 0 1 . 0
0   180 0 1 . 0
0   180 0 0 . 0
0   193 0 1 . 0
0   201 0 1 . 0
0   208 0 1 . 0
0   238 0 0 . 0
0   263 0 0 . 0
0   265 0 0 . 0
1  -437 1 0 . .
1  -434 1 1 . .
1  -430 1 1 . .
1   -28 0 0 . .
1   -20 0 0 . .
1   -20 0 0 . .
1     0 0 0 . .
1    14 0 1 . .
1    14 0 0 . .
1    25 0 1 . .
1    28 0 1 . .
1    30 0 1 . .
1    35 0 1 . .
1    36 0 0 . .
1    38 0 1 . .
1    42 0 0 . .
1    44 0 1 . .
1    46 0 1 . .
1    49 0 1 . .
1    52 0 1 . .
1    56 0 1 . .
1    58 0 1 . .
1    77 0 0 . .
1    79 0 0 . .
1    86 0 1 . .
end
label values before_surg_2yr yesno_lab
label def yesno_lab 0 "NO", modify
label def yesno_lab 1 "YES", modify
label values active_rehab labels0
label def labels0 0 "NO", modify
label def labels0 1 "yes", modify