How to calculate monthly hospital visits and mortality rates in large clinical data?

[KiranSaqib]

I have a large clinical data set for 2017-2021 and  have two questions;

1. How to calculate monthly hospital visits and mortality rates using sas eg for time series analysis procedure? I need the code please.

2.There are multiple entries per single Id at different time points,due to different reasons for visits. How to use do loop to create a variable "number of episode" per subject id? Would this factor be considered while calculating rates? I would highly appreciate your help since I don't have a programming background.

Thanks in advance.

[TomKari]

Okay, first some questions that will make it easier to provide advice:

1. You say a "large" clinical data set. Roughly how many records? Is it a flat file, a SAS dataset, or in a database?

2. I suggest you post a sample of a couple of dozen records. They can be fake, or have bits redacted, as long as we can see the fields that are important. Ideally, use this post to create some data for us to experiment with:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Again, don't post anything confidential.

Tom

[KiranSaqib]

Thank you for your support.

 I am working on a database (my study sample would be in millions).

Below is the table  ;

Patient-ID	Unique record ID	Visit date	Reason for Visit
1	11021	05 Jan 2018	Diabetes
1	11021	01 Feb 2019	Asthma
2	11025	01 March 2020	Asthma
1	11021	01 March 2018	Hypertension
3	11034	01 April 2017	Diabetes
1	11021	01 Jul 2019	Asthma
4	11078	01 Oct 2020	Hypertension
2	11025	01 April 2021	Asthma

[ballardw]

"Mortality rate" tends to imply some sort of death rate. You do not show anything resembling that. So you need to define how you are calculating a "mortality rate" from the shown example data.

We ask for example data in the form of data step code because then we do not need to guess about variable types, which especially with dates, makes a big difference of what we can do. Another reason is to clarify what the actual names of your SAS variables may be. You show 4 columns and none of them are a valid SAS variable name as shown.

Your "unique record number" is a bit suspect as it appears that your value is exactly the same for each record for a specific patient id. If the patient id will work for determining individuals then the "unique record id" would not be helpful from the example shown.

If your "visit date" variable is a SAS date value you can get a count of visit by month by using format that includes month and year in proc freq such as:

Proc Freq data=yourdatasetname;
   tables visitdate;
   format visitdate yymon7.;
run;

If you do not have SAS date values then you would need to use a data step to add such a variable using the input function.

[KiranSaqib]

Hi,

Thank you for your response. A little elaboration below.

My variables in database include:

1. Unique_ID :  id for each person  ( I have multiple entries agianst these on different dates)

2. Visit_date:  Date of visit (In SAS format) 

3. Med_diag:  Diagnosis OR reason for visit

4. dth_date:  death date for those who died

I hope I am able to explain it now.

[ballardw]

---

@KiranSaqib wrote:

Hi,

Thank you for your response. A little elaboration below.

My variables in database include:

1. Unique_ID :  id for each person  ( I have multiple entries agianst these on different dates)

2. Visit_date:  Date of visit (In SAS format) 

3. Med_diag:  Diagnosis OR reason for visit

4. dth_date:  death date for those who died

I hope I am able to explain it now.

---

"Rate" typically means there is a denominator and numerator involved. What your yours? This is pretty import to define exactly what the denominator would be when you have the same person in your data multiple times.

[KiranSaqib]

Since it's monthly rate for each year from 2017-2021.
Numerator would be number of visits per month while denominator should be 12 for each year seperately.