Using patient ID and admission date to find readmissions

[yoey hon]

Hi all,

I'm new to Stata and have a question regarding this hospital dataset I'm working on. The dataset contains a size of more than 700,000 patients, and 40 variables, all linked to a unique patient ID for all patients admitted to the hospital.

One person only has one patient ID but can have multiple admission episodes ID

I want to find out the number of readmissions for the same patient within 30 days and then relate those episodes with all other existing variables in the dataset. I have been trying to reformat the date and use various commands, but all didn't work. Can someone please provide some insights? Thank you so much!

Patient ID	Episode ID	Admission Date	Separation Date	Readmission within 30 days?
1A2B4DXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX	43434343	09/27/2018 00:00:00	09/28/2018 00:00:00	?

[Joseph Coveney]

I want to find out the number of readmissions for the same patient within 30 days and then relate those episodes with all other existing variables in the dataset.

I'm not able to follow you on this, on just what it is you're after.

For example, "the number of readmissions for the same patient within 30 days". Thirty days of what? The patient's first admission? A rolling thirty-day window?

Likewise, I cannot get what you mean by "relate those episodes with all other existing variables in the dataset".

[yoey hon]

I'm not able to follow you on this, on just what it is you're after.

For example, "the number of readmissions for the same patient within 30 days". Thirty days of what? The patient's first admission? A rolling thirty-day window?

Likewise, I cannot get what you mean by "relate those episodes with all other existing variables in the dataset".

Apologies, let me clarify: readmission within 30 days from the patient’s first admission.

Once the number of readmission per patient is found, I’m hoping to analyse this result with the other variables available in the dataset.

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input str37 patient_id long episode_id str20 admission_date str19 separation_date
"1A2B4DXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX " 43434343 "09/27/2018 00:00:00 " "09/28/2018 00:00:00"
end

//    CREATE STATA CLOCK VARIABLES, AND EXTRACT THE DATES FROM THEM
foreach v of varlist *_date {
    local w: subinstr local v "_date" "_dttm"
    gen double `w' = clock(`v', "MDYhms"), after(`v')
    assert missing(`w') == missing(`v')
    format `w' %tc
    drop `v'
    gen `v' = dofc(`w'), before(`w')
    format `v' %td
}

rangestat (count) readmissions = episode_id, by(patient_id) interval(admission_date, 1, 30)
replace readmissions = 0 if missing(readmissions)

The first part of the code takes your admission_date and separation_date variables (which I'm assuming you have as string variables) and converts them to Stata clock variables, and then create new date variables by extracting the date from the clock variable. These new variables are suitable for calculations.

The -rangestat- command is written by Robert Picard, Nick Cox, and Roberto Ferrer, and is available from SSC.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 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.

[yoey hon]

* Example generated by -dataex-. For more info, type help dataex
clear
input str37 patient_id long episode_id str20 admission_date str19 separation_date
"1A2B4DXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX " 43434343 "09/27/2018 00:00:00 " "09/28/2018 00:00:00"
end

// CREATE STATA CLOCK VARIABLES, AND EXTRACT THE DATES FROM THEM
foreach v of varlist *_date {
local w: subinstr local v "_date" "_dttm"
gen double `w' = clock(`v', "MDYhms"), after(`v')
assert missing(`w') == missing(`v')
format `w' %tc
drop `v'
gen `v' = dofc(`w'), before(`w')
format `v' %td
}

rangestat (count) readmissions = episode_id, by(patient_id) interval(admission_date, 1, 30)
replace readmissions = 0 if missing(readmissions)

The first part of the code takes your admission_date and separation_date variables (which I'm assuming you have as string variables) and converts them to Stata clock variables, and then create new date variables by extracting the date from the clock variable. These new variables are suitable for calculations.

The -rangestat- command is written by Robert Picard, Nick Cox, and Roberto Ferrer, and is available from SSC.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 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.

Hi Clyde, thank you so much for your detailed explanation.

Unfortunately, I received an error code when I used the below command as variable already defined.
input str37 patient_id long episode_id str20 admission_date str19 separation_date Also, when I created the clock varilables, I received an error code of variable date not found. Please let me know if I am missing anything? Thank you!

[Hemanshu Kumar]

These errors can only occur due to mistakes on your part in using Clyde's code.

Unfortunately, I received an error code when I used the below command as variable already defined.

Did you also include the command just before that one, i.e.

clear

This removes all variables from memory. So your error could have only come up if you missed doing this.

Also, when I created the clock varilables, I received an error code of variable date not found. Please let me know if I am missing anything? Thank you!

Again, check whether you copied Clyde's code correctly. For instance, did you type this line exactly in this way?

foreach v of varlist *_date {

Did you perhaps miss the underscore between * and date?

[yoey hon]

These errors can only occur due to mistakes on your part in using Clyde's code.



Did you also include the command just before that one, i.e.

clear

This removes all variables from memory. So your error could have only come up if you missed doing this.



Again, check whether you copied Clyde's code correctly. For instance, did you type this line exactly in this way?

foreach v of varlist *_date {

Did you perhaps miss the underscore between * and date?

Thank you Hemanshu, I have copied the code exactly other than including the 'clear' in the beginning, thanks for flagging that. However, the command seems also to reflect error as invalid varname and variable date not found when creating clock variables.
"1A2B4DXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX " invalid varname

[Clyde Schechter]

I have run this code on my own setup. It gives me no error messages, and it produces a correct result.

The error messages you are getting can only arise if you are not using the code correctly, or if there is something wrong with your Stata installation.

Use your computer's copy/paste to transfer the code directly from this webpage into your do-file editor and then click on the Execute button (the rightmost one on the toolbar of the do-file editor). If it does not run correctly, go to your Results window, select the entire output you got from the (attempted) run, and again use your computer's copy/paste to transfer all of that (don't leave out anything and don't edit it in any way) into a new post here so we can see exactly what is going on.

[Hemanshu Kumar]

I would suggest there is still a problem with the way you are copying the code. The lines

input str37 patient_id long episode_id str20 admission_date str19 separation_date
"1A2B4DXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX " 43434343 "09/27/2018 00:00:00 " "09/28/2018 00:00:00"

need to be two separate lines -- not just visually, but actually separated by a carriage return. To make sure of this, press <enter> or <return> after the first line.

[yoey hon]

I have run this code on my own setup. It gives me no error messages, and it produces a correct result.

The error messages you are getting can only arise if you are not using the code correctly, or if there is something wrong with your Stata installation.

Use your computer's copy/paste to transfer the code directly from this webpage into your do-file editor and then click on the Execute button (the rightmost one on the toolbar of the do-file editor). If it does not run correctly, go to your Results window, select the entire output you got from the (attempted) run, and again use your computer's copy/paste to transfer all of that (don't leave out anything and don't edit it in any way) into a new post here so we can see exactly what is going on.

Thank you both, please see the attached screenshot, I'm working on a remote desktop which prevents me from copying the code directly from my laptop to the virtual machine.

[yoey hon]

I have run this code on my own setup. It gives me no error messages, and it produces a correct result.

The error messages you are getting can only arise if you are not using the code correctly, or if there is something wrong with your Stata installation.

Use your computer's copy/paste to transfer the code directly from this webpage into your do-file editor and then click on the Execute button (the rightmost one on the toolbar of the do-file editor). If it does not run correctly, go to your Results window, select the entire output you got from the (attempted) run, and again use your computer's copy/paste to transfer all of that (don't leave out anything and don't edit it in any way) into a new post here so we can see exactly what is going on.

I've tried multiple times and still received the same error code as above. Would there be any other ways to achieve the intended result? Thank you so much for your help!

[Clyde Schechter]

Sorry, I somehow never saw #10 until now. So, Stata says that it cannot find any *_date variables. Stata is never wrong when it says that. So the problem is that you have no variables named *_date in your data set (or if you had them to start with, they have somehow been deleted or renamed before reaching that -foreach- loop).

As, despite my request, you have not shown example data in a correct and usable way (-dataex-), I can only, at this point, guess about the problem. Based on past experience the most likely cause is that your data set's variable names are different. For example, I wrote the code on the assumption that what you called Admission Date in #1 (which is not a possible Stata variable name because blank spaces are not allowed) is really called admission_date. But maybe in your data set it's actually called AdmissionDate or Admission_Date or something like that. In that case, *_date in the code won't match with the variable names: variable names in Stata are case sensitive, and they are also sensitive to the presence or absence of underscore (_) characters. So the question I pose to you is: what are the actual names of your date variables? If they do not end, exactly, in _date, then the above code is not suited to them. You will either need to rename the variables so that they work with the code, or modify the code to work with the actual variable names.

If this does not solve your problem, please post back using -dataex- to show actual example data from your real Stata data set. Please do not waste your time or mine or anybody else's showing your example data in some other way. Only -dataex- is sufficiently informative to help solve your problem. Also, again, use your computer's copy/paste functions to show the exact code you ran and the exact output you got from Stata. (Please do not use a screenshot--screenshots are not reliably readable in this Forum. The one you posted in #10, fortunately, is. But, often as not, they just show up as glyphs with no actual content. Use copy/paste instead.)