Creating a variable that counts how many months since you started receiving a 1 for a certain variable

[Collin James]

Hi,

I have code set up in the following way (below). I have three important variables, mci= an id for a person (I changed these because it is private data).. Activemonth_final=the year month that it is in the data (looks like 2020m1, 2019m12, etc in my data). DHS_all_act_ind, which =1 if someone received a DHS service in the activemonth_final it is, and 0 otherwise. I want to create a variable that tells me how LONG you have been receiving DHS. For instance, if you are MCI 9383, if it is activemonth_final 697, then you would have a zero for this variable, whereas if it is one month after activemonth_final 736 (the first month where MCI 9383 receives this service) it will show a value of 1, then the next month a value of 2, etc. So basically, whenever the first time you have a 1 for dhs_all_act_ind in the data, I want the variable I create to count the months it has been since then (data at month-year-person level). Can you help with this? Thanks!

	
	
* Example generated by -dataex-. For more info, type help dataex
clear
input long mci float activemonth_final byte dhs_all_act_ind
9310 696 1
9314 696 0
9323 725 0
9383 697 0
9383 736 1
end
format %tm activemonth_final

[William Lisowski]

Here is sample code that may start you in a useful direction. I changed your example data to better test and demonstrate the code.

* Example generated by -dataex-. For more info, type help dataex
clear
input long mci float activemonth_final byte dhs_all_act_ind
9383 735 0
9383 736 1
9383 737 0
9383 738 1
9383 739 0
9383 749 0
end
format %tm activemonth_final

sort mci activemonth_final
by mci: egen first = min(cond(dhs_all_act_ind==1,activemonth_final,.))
format %tm first 
generate since = max(0,activemonth_final-first)
list, clean abbreviate(20)

. list, clean abbreviate(20)

        mci   activemonth_final   dhs_all_act_ind    first   since  
  1.   9383              2021m4                 0   2021m5       0  
  2.   9383              2021m5                 1   2021m5       0  
  3.   9383              2021m6                 0   2021m5       1  
  4.   9383              2021m7                 1   2021m5       2  
  5.   9383              2021m8                 0   2021m5       3  
  6.   9383              2022m6                 0   2021m5      13