Panel data, keep first instance of each diagnose.

[Jesper Eriksson]

Hi Statalisters,


I am trying to calculate the time from treatment in the intensive care unit (ICU) until patients are diagnosed with different diagnoses. In other words the time from “index_date” until the first instance of a specific diagnose is established. I want to calculate the time between ICU to several different diagnoses.


Code book:
“patient_id" - Patient number
“index_date" - The date when the patient was treated in the intensive care unit.
“diagnose_date" - The date when the patient recieved a new diagnose. A patient can have several identical diagnose_dates. Some patients does not have a diagnose_date, they have not acquired any diagnoses after their ICU treatment.
“all_diagnoses" - All the diagnoses the respective patient has aquired since discharge up until the respective diagnose_date. Example “I819”, "R00-R99”, “J189" etc. So the last diagnose_date for each patient includes all the diagnoses the patient has acquired since their ICU treatment. The cumulative sum of diagnoses if you so will.


I guess I have to split all_diagnoses into separate variables, keep the first instance of each diagnose per patient and thereafter calculate the delta time between index_date and a specific diagnose.

However I can not figure out how to split all_diagnoses to different variables and keep the first instance of each diagnose for each patient. I am only interested in the time to the first occurance of each diagnoses.
Any ideas?

* Example generated by -dataex-. For more info, type help dataex
clear
input double patient_id float(index_date diagnose_date) strL all_diagnoses
 1 21949     . `""""'                                                                                                                        
 2 21996 22006 `""R00-R99" "A418" "I109" "N189" "U82-U85" "U00-U49" "A00-A09" "E119" """'                                                    
 2 21996 22022 `""N184" "R00-R99" "E117" "A418" "I109" "N189" "U82-U85" "U00-U49" "A00-A09" "E119" """'                                      
 2 21996 22028 `""N184" "R00-R99" "E117" "A418" "Z00-Z99" "I109" "N189" "U82-U85" "U00-U49" "A00-A09" "E119" """'                            
 2 21996 22076 `""Z492" "N184" "R00-R99" "Z00-Z99" "E117" "A418" "N185" "I109" "U82-U85" "N189" "U00-U49" "A00-A09" "E119" """'              
 2 21996 22175 `""N185" "Z492" "N184" "R00-R99" "I109" "Z00-Z99" "E117" "A418" "U82-U85" "N189" "U00-U49" "A00-A09" "E119" """'              
 2 21996 22218 `""N185" "Z492" "N184" "R00-R99" "Z00-Z99" "I109" "E117" "A418" "U82-U85" "N189" "U00-U49" "A00-A09" "E119" """'              
 2 21996 22322 `""N185" "Z492" "N184" "R00-R99" "E112" "Z00-Z99" "I109" "E117" "A418" "U00-U49" "U82-U85" "N189" "Z992" "A00-A09" "E119" """'
 3 22017 22196 `""U98-U99" """'                                                                                                              
 3 22017 22201 `""U00-U49" "U98-U99" "J128" "R00-R99" "K55-K64" """'                                                                         
 4 22026 22148 `""R00-R99" "F172" """'                                                                                                       
 4 22026 22150 `""E70-E90" "R00-R99" "F172" """'                                                                                             
 4 22026 22148 `""R00-R99" """'                                                                                                              
 4 22026 22153 `""G473" "E70-E90" "R00-R99" "F172" """'                                                                                      
 4 22026 22167 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22167 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22228 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22228 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22243 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22258 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22312 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22319 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22333 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 4 22026 22349 `""R00-R99" "G473" "E70-E90" "B35-B49" "F172" """'                                                                            
 5 22056 22065 `""U00-U49" "I109" "E119" """'                                                                                                
 6 22013 22062 `""U00-U49" "J9600" "J809X" "R00-R99" "N179" "I460" "D60-D64" "M05-M14" "E65-E68" """'                                        
 6 22013 22013 `""J9600" "R00-R99" "I460" """'                                                                                               
 6 22013 22083 `""G638" "I269" "U00-U49" "J9600" "J809X" "R00-R99" "G728" "N179" "I460" "D60-D64" "M05-M14" "E65-E68" """'                   
 6 22013 22083 `""I269" "U00-U49" "J9600" "J809X" "R00-R99" "N179" "I460" "D60-D64" "M05-M14" "E65-E68" """'                                 
 6 22013 22166 `""G728" "G638" "I269" "U00-U49" "J9600" "J809X" "R00-R99" "N179" "I460" "D60-D64" "M05-M14" "E65-E68" """'                   
 6 22013 22189 `""Z00-Z99" "G728" "G638" "I269" "U00-U49" "J9600" "J809X" "R00-R99" "N179" "I460" "D60-D64" "M05-M14" "E65-E68" """'         
 6 22013 22231 `""I269" "Z00-Z99" "G728" "G638" "U00-U49" "J9600" "J809X" "R00-R99" "N179" "I460" "D60-D64" "M05-M14" "E65-E68" """'         
 6 22013 22235 `""Z00-Z99" "I269" "G728" "G638" "U00-U49" "J9600" "J809X" "R00-R99" "N179" "I460" "D60-D64" "M05-M14" "E65-E68" """'         
 6 22013 22334 `""R00-R99" "Z00-Z99" "I269" "G728" "G638" "U00-U49" "J9600" "J809X" "N179" "I460" "D60-D64" "M05-M14" "E65-E68" """'         
 7 21216 21217 `""Z00-Z99" """'                                                                                                              
 7 21216 21294 `""M70-M79" "Z00-Z99" """'                                                                                                    
 7 21216 21333 `""C60" "M70-M79" "Z00-Z99" """'                                                                                              
 7 21216 21394 `""M05-M14" "C60" "M70-M79" "Z00-Z99" """'                                                                                    
 7 21216 21418 `""M70-M79" "M05-M14" "C60" "Z00-Z99" """'                                                                                    
 7 21216 21419 `""M70-M79" "M05-M14" "C60" "Z00-Z99" """'                                                                                    
 7 21216 21424 `""Z00-Z99" "M70-M79" "M05-M14" "C60" """'                                                                                    
 7 21216 21508 `""C60" "Z00-Z99" "M70-M79" "M05-M14" """'                                                                                    
 7 21216 21591 `""M45-M49" "C60" "Z00-Z99" "M70-M79" "M05-M14" """'                                                                          
 7 21216 21594 `""M45-M49" "C60" "Z00-Z99" "M70-M79" "M05-M14" """'                                                                          
 7 21216 21599 `""C60" "M45-M49" "Z00-Z99" "M70-M79" "M05-M14" """'                                                                          
 7 21216 21686 `""Z00-Z99" "C60" "M45-M49" "M70-M79" "M05-M14" """'                                                                          
 7 21216 21844 `""M20-M25" "Z00-Z99" "C60" "M45-M49" "M70-M79" "M05-M14" "M15-M19" """'                                                      
 7 21216 21878 `""Z00-Z99" "M20-M25" "C60" "M45-M49" "M70-M79" "M05-M14" "M15-M19" """'                                                      
 7 21216 21923 `""M20-M25" "Z00-Z99" "C60" "M45-M49" "M70-M79" "M05-M14" "M15-M19" """'                                                      
 7 21216 21936 `""M86-M90" "M20-M25" "Z00-Z99" "C60" "M45-M49" "M70-M79" "M05-M14" "M15-M19" """'                                            
 8 22002     . `""""'                                                                                                                        
 9 21274 21337 `""O00-O999" """'                                                                                                             
 9 21274 21405 `""O00-O999" """'                                                                                                             
10 22049 22327 `""S00-T98" """'                                                                                                              
11 22006 22186 `""S00-T98" """'                                                                                                              
12 19726     . `""""'                                                                                                                        
13 22031     . `""""'                                                                                                                        
14 22003     . `""""'                                                                                                                        
15 22019 22033 `""U00-U49" "J128" "J809C" "E65-E68" "J459" """'                                                                              
15 22019 22046 `""U00-U49" "R572" "J128" "J809C" "E65-E68" "J459" "Z00-Z99" """'                                                             
16 21999     . `""""'                                                                                                                        
17 22115     . `""""'                                                                                                                        
18 21267 21333 `""C91" """'                                                                                                                  
18 21267 21431 `""C91" """'                                                                                                                  
18 21267 21739 `""H00-H599" "C91" """'                                                                                                       
18 21267 21822 `""C91" "H00-H599" """'                                                                                                       
19 22057     . `""""'                                                                                                                        
20 22071 22075 `""U00-U49" "R00-R99" "I269" "I482" "F329" "M50-M54" """'                                                                     
20 22071 22071 `""R00-R99" """'                                                                                                              
21 22027     . `""""'                                                                                                                        
22 22123     . `""""'                                                                                                                        
23 22019     . `""""'                                                                                                                        
24 20163 20166 `""J100" "J960" "J441" "F209" "Z00-Z99" "E65-E68" "K40-K46" """'                                                              
25 22001     . `""""'                                                                                                                        
26 21288 21294 `""J157" "R00-R99" "J100" "Z00-Z99" "G803A" "F720" "H00-H599" "M40-M43" """'                                                  
26 21288 21288 `""R00-R99" """'                                                                                                              
26 21288 21391 `""R00-R99" "J157" "G80-" "J100" "Z00-Z99" "G803A" "F720" "H00-H599" "M40-M43" """'                                           
26 21288 21398 `""G803A" "R00-R99" "J157" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" "M40-M43" """'                                           
26 21288 21410 `""G803A" "R00-R99" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                                           
26 21288 21456 `""R00-R99" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                                           
26 21288 21503 `""G809" "R00-R99" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                                    
26 21288 21595 `""G809" "R00-R99" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                                    
26 21288 21636 `""G809" "R00-R99" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                                    
26 21288 21664 `""G809" "R00-R99" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                                    
26 21288 21693 `""G809" "R00-R99" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                                    
26 21288 21719 `""J189" "G809" "R00-R99" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                             
26 21288 21780 `""R00-R99" "J189" "G809" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                             
26 21288 21789 `""G809" "R00-R99" "J189" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "Z00-Z99" "H00-H599" """'                             
26 21288 21810 `""Z00-Z99" "G809" "R00-R99" "J189" "G803A" "J157" "M40-M43" "F720" "G80-" "J100" "H00-H599" """'                             
26 21288 21851 `""G803A" "Z00-Z99" "G809" "R00-R99" "J189" "J157" "M40-M43" "F720" "G80-" "J100" "H00-H599" """'                             
26 21288 21864 `""G809" "G803A" "Z00-Z99" "R00-R99" "J189" "J157" "M40-M43" "F720" "G80-" "J100" "H00-H599" """'                             
26 21288 21885 `""Z00-Z99" "G809" "G803A" "R00-R99" "J189" "J157" "M40-M43" "F720" "G80-" "J100" "H00-H599" """'                             
26 21288 21892 `""Z00-Z99" "G809" "G803A" "R00-R99" "J189" "J157" "M40-M43" "F720" "G80-" "J100" "H00-H599" """'                             
26 21288 21989 `""M70-M79" "Z00-Z99" "G809" "G803A" "R00-R99" "J189" "J157" "M40-M43" "F720" "G80-" "J100" "H00-H599" """'                   
27 22025 22162 `""F739" """'                                                                                                                 
27 22025 22343 `""K40-K46" "F739" """'                                                                                                       
28 22070 22117 `""K50-K52" """'                                                                                                              
28 22070 22148 `""K50-K52" """'                                                                                                              
28 22070 22189 `""K50-K52" """'                                                                                                              
28 22070 22221 `""K50-K52" """'                                                                                                              
end
format %td index_date
format %td diagnose_date

All help much appreciated!

Best regards, Jesper Eriksson

[Daniel Schaefer]

Is index_date always constant within patent ID's? That is, each patent has only one index_date? If so you can just get the number of days from the index_date to the diagnosis_date like this:

gen days = diagnose_date - index_date

If you want this for the first diagnosis only, then this should work:

bysort patient_id (diagnose_date), sort: gen days = diagnose_date - index_date if _n == 1

However I can not figure out how to split all_diagnoses to different variables and keep the first instance of each diagnose for each patient.

I think your asking about the -reshape- command here, but I don't think you actually need it for this.

Edit: Oh, I see, you want the time to the first occurrence of each unique diagnosis.

[Clyde Schechter]

I interpret #1 to mean that a series of variables is wanted, one for each diagnosis, showing the earliest date, if any, at which the patient gets that diagnosis. If that's what is wanted:

//    FIRST GET A COMPREHENSIVE LIST OF DISTINCT DIAGNOSES
frame put all_diagnoses, into(diagnoses)
frame diagnoses {
    split all_diagnoses, gen(dx)
    drop all_diagnoses
    gen `c(obs_t)' obs_no = _n
    reshape long dx, i(obs_no)
    drop obs_no _j
    by dx, sort: keep if _n == 1
    levelsof dx, local(diagnoses)
}
frame drop diagnoses

//    NOW FIND A FIRST DIAGNOSIS DATE (IF ANY) FOR EACH DIAGNOSIS
foreach d of local diagnoses {
    local dd = strtoname(`"`d'"')
    by patient_id (diagnose_date), sort: egen first_dx_`dd' ///
        =min(cond(strpos(all_diagnoses, `"`d'"'), diagnose_date, .))
    format first_dx_`dd' %td
}

[Daniel Schaefer]

On a careful read, I see that OP would like a separate variable for each diagnoses, but I am under the impression that OP wants to calculate the amount of time (presumably in days). Here is my implementation.

replace all_diagnoses = subinstr(all_diagnoses, "-", "_", .)
replace all_diagnoses = subinstr(all_diagnoses, `"""', "", .)
split all_diagnoses

foreach diagnoses of varlist all_diagnoses*{
    if "`diagnoses'" == "all_diagnoses"{
        continue
    }
    quietly levelsof `diagnoses', local(levels)
    foreach level of local levels {
        capture gen d_`level' = 0
        replace d_`level' = 1 if `diagnoses' == "`level'"
    }
}

foreach var of varlist d_* {
    bysort patient_id (diagnose_date), sort: replace `var' = sum(`var')
    gen days_to_`var' = diagnose_date - index_date if `var' == 1
}

[Daniel Schaefer]

As a cross-validation step, I did a quick follow up to see if Clyde Schechter and I have equivalent procedures. In order to do that, I modified my solution by adding a new line to the end of the last for loop in #4.

foreach var of varlist d_* {
    bysort patient_id (diagnose_date), sort: replace `var' = sum(`var')
    gen days_to_`var' = diagnose_date - index_date if `var' == 1
    bysort patient_id (days_to_`var'), sort: replace days_to_`var' = days_to_`var'[1]
}

I then test like so:

foreach var in C60 C91 E70_E90 F739 G473 G638 G728 G803A G809 H00_H599 I269 J100 J157 J189 J9600 K40_K46 K50_K52 M05_M14 M20_M25 M45_M49 M70_M79 M86_M90 N184 N185 O00_O999 R00_R99 S00_T98 U00_U49 U98_U99 Z00_Z99 Z492 A418 F172 I109 J128 J960 R572 E117 E119 G80_ I460 J441 J809C J809X B35_B49 E65_E68 F209 F720 I482 M40_M43 N189 E112 F329 J459 K55_K64 N179 U82_U85 M50_M54 A00_A09 D60_D64 M15_M19 Z992{
    display "`var'"
    gen first_dx_`var'_diff = first_dx__`var'_ - index_date
    capture noisily assert first_dx_`var'_diff == days_to_d_`var'
}

It appears that if you run my code first it changes the state of the dataset in such a way as to create a bug in Clyde's solution. If you run Clyde's solution first, you should see that they are equivalent.

[Jesper Eriksson]

Thank you all!

To be more precise:
Yes, index_date is constant within patient_id.
I would like to find the date for each occuring diagnosis (if any), for every patient. This to later calculate the time between index_date and the date for each diagnose.

For example; patient_id #3 has received diagnoses on two occasions. On the first occasion (SIF-date 22916) he/she received the diagnose U98-U99. In the second occasion (SIF-date 22201) he/she received four additional diagnoses (U98-U99 appears on this second occasion as well, but occured first on the first occasion, it is thus repeated).
I would like to get, for patient_id #3 five variables, each named as the five diagnoses (U98-U99 etc) as well as the respective date patient_id #3 first received each specific diagnose.

The ultimate goal is to be able to calculate the time from index_date to each occuring diagnose for every patient.


I am writing on my mobile phone now, I will test your suggestions when I get home. I am sure they will work fine!

[Jesper Eriksson]

Dear Clyde and Daniel, many thanks for your help. Both solutions works perfect (as you checked Daniel). Wish you a wonderful 2024.