Calculation from different observation

[Vini Fardhdiani]

Dear Stata Master,

I have a question about how to calculate a value coming from 2 variables and 2 different observations.

This is the example of my dataset:

ID number	Drug name	Date start	Date end	# of days interupted
1	Levofloxacin	1 Jan 2022	5 Jan 2022
1	Levofloxacin	15 Jan 2022
1	Cycloserine	1 Jan 2022
1	Bedaquiline	1 Jan 2022	31 Jan 2022
1	Bedaquiline	1 Mar 2022	30 April 2022
1	Bedaquiline	5 May 2022	15 May 2022

I need to calculate # of days treatment interrupted, which come from date start of an observation minus date end the previous observation, for the same drug name.
For example, here for Levofloxacin= 10 days interruption (from 5 Jan to 15 Jan)
For Cycloserine = no interruption
For Bedaquiline = 1st interruption = 29 days (from 31 Jan to 1 Mar), 2nd interruption = 56 days (30 Apr to 5 May), and 3rd interruption = 106 days (15 May up to now).

Is there a way to do this calculation automatically?

Thank you in advance for your support.

Vini

[Joro Kolev]

You should show a sample of your data using -dataex-.

But I think that if you just take the difference of the two variables you will get what you want.

gen interruption = day_end - day_start.

[Carlo Lazzaro]

Vini:
what you're after does not seem to be totally consistent with your data (BTW: as Joro receommended, please use -dataex- to share your data example/excerpt. Thanks).
Please double check whether what follows is on the right track (the total is still missing):

. generate double start_time = date(Date_start , "DMY")

. generate double end_time = date(Date_end , "DMY")

. format start_time end_time %td

. drop interruption

. bysort ID: gen interruption= start_time[_n+1]-end_time


. list

     +------------------------------------------------------------------------------------+
     | ID      Drug_name    Date_start        Date_end   start_t~e    end_time   interr~n |
     |------------------------------------------------------------------------------------|
  1. |  1   Levofloxacin    1 Jan 2022      5 Jan 2022   01jan2022   05jan2022         10 |
  2. |  1   Levofloxacin   15 Jan 2022                   15jan2022           .          . |
  3. |  1    Cycloserine    1 Jan 2022                   01jan2022           .          . |
  4. |  1    Bedaquiline    1 Jan 2022     31 Jan 2022   01jan2022   31jan2022         29 |
  5. |  1    Bedaquiline    1 Mar 2022   30 April 2022   01mar2022   30apr2022          5 |
     |------------------------------------------------------------------------------------|
  6. |  1    Bedaquiline    5 May 2022     15 May 2022   05may2022   15may2022          . |
     +------------------------------------------------------------------------------------+

.

...and the total follows:

. g num_Drug_name=.

. replace num_Drug_name=1 if Drug_name=="Levofloxacin"

. replace num_Drug_name=2 if Drug_name=="Cycloserine"

. replace num_Drug_name=3 if Drug_name=="Bedaquiline"

. label define num_Drug_name 1 "Levofloxacin" 2 "Cycloserine" 3 "Bedaquiline"

. label val num_Drug_name num_Drug_name

. bysort num_Drug_name : gen wanted=sum( interruption) if interruption !=.

. list

     +------------------------------------------------------------------------------------------------------------+
     | ID      Drug_name    Date_start        Date_end   start_t~e    end_time   interr~n   num_Drug_n~e   wanted |
     |------------------------------------------------------------------------------------------------------------|
  1. |  1   Levofloxacin    1 Jan 2022      5 Jan 2022   01jan2022   05jan2022         10   Levofloxacin       10 |
  2. |  1   Levofloxacin   15 Jan 2022                   15jan2022           .          .   Levofloxacin        . |
  3. |  1    Cycloserine                                         .           .          .    Cycloserine        . |
  4. |  1    Bedaquiline                   31 Jan 2022           .   31jan2022         29    Bedaquiline       29 |
  5. |  1    Bedaquiline    1 Mar 2022   30 April 2022   01mar2022   30apr2022          5    Bedaquiline       34 |
     |------------------------------------------------------------------------------------------------------------|
  6. |  1    Bedaquiline    5 May 2022     15 May 2022   05may2022   15may2022          .    Bedaquiline        . |
     +------------------------------------------------------------------------------------------------------------+

.

[Hemanshu Kumar]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte idnumber str12 drugname str11(datestart dateend)
1 "Levofloxacin" "01 Jan 2022" "05 Jan 2022"
1 "Levofloxacin" "15 Jan 2022" ""           
1 "Cycloserine"  "01 Jan 2022" ""           
1 "Bedaquiline"  "01 Jan 2022" "31 Jan 2022"
1 "Bedaquiline"  "01 Mar 2022" "30 Apr 2022"
1 "Bedaquiline"  "05 May 2022" "15 May 2022"
end

generate double start_date = date(datestart,"DMY")
generate double end_date = date(dateend,"DMY")
format start_date end_date %td

gen x= _n
sort idnumber drugname start_date
by idnumber drugname: gen interruption = start_date[_n+1]-end_date
by idnumber drugname: replace interruption = date("`=c(current_date)'","DMY") - end_date if !missing(end_date) & _n == _N
sort x
drop x start_date end_date
list, clean noobs

which produces:

    idnumber       drugname    datestart      dateend   interr~n  
           1   Levofloxacin   01/01/2022   05/01/2022         10  
           1   Levofloxacin   15/01/2022                       .  
           1    Cycloserine   01/01/2022                       .  
           1    Bedaquiline   01/01/2022   31/01/2022         29  
           1    Bedaquiline   01/03/2022   30/04/2022          5  
           1    Bedaquiline   05/05/2022   15/05/2022        106

[Vini Fardhdiani]

Hello Carlo, thank you so much for this, it works like a charm!
My apology for my ignorance Joro and sending the file using table from here.
For my future reference, how can I build a -dataex-?
I'm sorry if this sounds like a stupid question, I just started using stata.

Vini

[Carlo Lazzaro]

Vini:
see -help dataex-.

[Hemanshu Kumar]

Vini Fardhdiani Carlo's solution in #3 produces totals. I'm not sure you want that. Your original post seemed to suggest you wanted the duration of interruptions, including the interruption from the last date till "now" (from your example of 106 days from 15th May till now). My code in #4 was designed to do that.

[Vini Fardhdiani]

Wow, thanks all, I managed to create the dataex thingy. I will use this format next time.

Hemanshu Kumar , somehow I missed your message! And you are right, I will add that step to get the interruption until today.

Thanks again everyone for your time to reply to my messages.

Vini