How to flag an event of interest in a longitudinal dataset

[Befikadu Wubishet]

Hi,

I have a hospital admission data in which every patient may have one to hundreds of repeated admissions. I want to create a variable to indicate me when patients have certain types of causes of admission identified by ICD codes, for instance ICD1, ICD2, ICD3. I am not sure if I should be using looping or something else.

I would be grateful to get any help.

Thank you,
Befikadu

[Clyde Schechter]

It is impossible to answer your question without seeing an example of your data, to know how it is organized. Please post back using the -dataex- command to show an example that illustrates the problem you wish to solve. Also make it clear whether you want to just single out certain ICD9 codes where they appear, or if you want to identify those patients who, on any admission, have one of your targeted diagnoses.

If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Befikadu Wubishet]

Dear Clyde,

Thank very much for the explanation. I am learning stata and your hand tips above already helped me heaps.

Please see below for the dataex sample of my dataset. I want to i) identify patients with Z06, Z06.1, Z06.2, Z06.8, Z06.9 diagnosis codes; and ii) calculate the yearly prevalence/proportion of all theses diagnoses together as a whole in the population.

Please let me know if you need further information.

Thank you again for the help!
Befikadu

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 study_id float first_proc_date str7 icd_code
"CASE_000001"     . "F03"    
"CASE_000001"     . "N19"    
"CASE_000001"     . "I509"   
"CASE_000001"     . "S019"   
"CASE_000001"     . "9602700"
"CASE_000003" 20179 "9555005"
"CASE_000003"     . "R634"   
"CASE_000003"     . "R470"   
"CASE_000003"     . "9555001"
"CASE_000003"     . "Z9664"  
"CASE_000003"     . "9555012"
"CASE_000003"     . "K219"   
"CASE_000003"     . "G309"   
"CASE_000003"     . "M1901"  
"CASE_000003"     . "R568"   
"CASE_000003"     . "R258"   
"CASE_000003"     . "I10"    
"CASE_000003"     . "R451"   
"CASE_000003"     . "I64"    
"CASE_000003"     . "9555002"
"CASE_000003"     . "K590"   
"CASE_000003"     . "Z515"   
"CASE_000003"     . "M170"   
"CASE_000003"     . "G819"   
"CASE_000003"     . "F009"   
"CASE_000003"     . "R32"    
"CASE_000003"     . "9555003"
"CASE_000003"     . "T801"   
"CASE_000003"     . "R13"    
"CASE_000004" 19540 "4751900"
"CASE_000004" 19967 "9619909"
"CASE_000004" 19997 "9555003"
"CASE_000004" 19997 "9220900"
"CASE_000004" 20058 "9220901"
"CASE_000004"     . "J440"   
"CASE_000004"     . "N390"   
"CASE_000004"     . "E119"   
"CASE_000004"     . "T841"   
"CASE_000004"     . "B952"   
"CASE_000004"     . "L891"   
"CASE_000004"     . "G309"   
"CASE_000004"     . "Z0661"  
"CASE_000004"     . "I500"   
"CASE_000004"     . "I500"   
"CASE_000004"     . "9251230"
"CASE_000004"     . "D509"   
"CASE_000004"     . "J189"   
"CASE_000004"     . "R32"    
"CASE_000004"     . "9555001"
"CASE_000004"     . "L899"   
"CASE_000004"     . "J9601"  
"CASE_000004"     . "R33"    
"CASE_000004"     . "I7023"  
"CASE_000004"     . "I10"    
"CASE_000004"     . "E119"   
"CASE_000004"     . "Z480"   
"CASE_000004"     . "9555002"
"CASE_000004"     . "9206200"
"CASE_000004"     . "F009"   
"CASE_000004"     . "A419"   
"CASE_000004"     . "E1151"  
"CASE_000004"     . "E1169"  
"CASE_000004"     . "E880"   
"CASE_000004"     . "9555000"
"CASE_000004"     . "9555001"
"CASE_000004"     . "G309"   
"CASE_000004"     . "N390"   
"CASE_000004"     . "D509"   
"CASE_000004"     . "E46"    
"CASE_000004"     . "S010"   
"CASE_000004"     . "B370"   
"CASE_000004"     . "S510"   
"CASE_000004"     . "J440"   
"CASE_000004"     . "F009"   
"CASE_000004"     . "Z8643"  
"CASE_000004"     . "9555000"
"CASE_000004"     . "S7211"  
"CASE_000004"     . "S510"   
"CASE_000004"     . "H010"   
"CASE_000004"     . "R15"    
"CASE_000004"     . "L891"   
"CASE_000004"     . "L891"   
"CASE_000004"     . "9555001"
"CASE_000004"     . "1370602"
"CASE_000004"     . "G309"   
"CASE_000004"     . "F009"   
"CASE_000004"     . "I500"   
"CASE_000004"     . "S010"   
"CASE_000004"     . "Z0668"  
"CASE_000004"     . "R509"   
"CASE_000004"     . "I500"   
"CASE_000004"     . "E1169"  
"CASE_000004"     . "G309"   
"CASE_000004"     . "9555003"
"CASE_000004"     . "R680"   
"CASE_000004"     . "Z8643"  
"CASE_000004"     . "N179"   
"CASE_000004"     . "Z8643"  
"CASE_000004"     . "B370"   
"CASE_000004"     . "D62"    
end
format %td first_proc_date

[Clyde Schechter]

icd10 generate focal_dx = icd_code, range(Z06 Z06.1 Z06.2 Z06.8 Z06.9)
by study_id, sort: egen has_focal_dx = max(focal_dx)
by study_id: gen study_id_flag = (_n == 1)
summ focal_dx if study_id_flag, meanonly
display "Prevalence proportion = " `r(mean)'

Note: your icd_code variable contains a fairly large number if invalid icd10 codes; and those aren't valid icd9 codes either, so I think you better take a close look at your data