Grouping assessments within stays within individuals

[Jenefer Jedele]

I have a dataset of assessments with identifiers for the patient and the admission and discharge dates identifying the separate stays. My data contain the first 4 columns in the table below. I am trying to create the fifth variable ("stay") which is an identifier of each stay within individual patient and groups assessments completed within a given stay.

The following code (Stata 15) gets me close, but does not start the "stay" value at '1' for each new ID.

sort ID admit discharge
egen stay = group(ID admit discharge)


The last column (stay_desired) shows the desired outcome.

ID	admit	discharge	assessment	stay	stay_desired
1	19-May-11	1-Apr-13	1	1	1
1	19-May-11	1-Apr-13	2	1	1
1	19-May-11	1-Apr-13	3	1	1
1	19-May-11	1-Apr-13	4	1	1
2	13-Apr-15	15-May-15	1	2	1
2	13-Apr-15	15-May-15	2	2	1
3	8-Feb-17	15-Feb-17	1	3	1
3	8-Feb-17	15-Feb-17	2	3	1
4	31-Oct-13	13-Nov-13	1	4	1
5	31-Jul-12	27-Aug-12	1	5	1
6	27-Feb-08	28-Mar-13	1	6	1
6	27-Feb-08	28-Mar-13	2	6	1
6	27-Feb-08	28-Mar-13	3	6	1
7	14-Feb-14	24-Feb-14	1	7	1
8	9-Apr-14	16-Apr-14	1	8	1
9	18-Mar-14	10-Apr-14	1	9	1
10	2-Jul-12	24-Jul-12	1	10	1
10	17-Dec-12	8-Jan-13	1	11	2
10	2-Jul-14	11-Jul-14	1	12	3

Thanks for any guidance!
Regards,
Jenefer

[William Lisowski]

Here is your data presented using dataex, which will make it possible for others to easily read it into Stata.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float(admit discharge) byte(assessment stay stay_desired)
 1 18766 19449 1  1 1
 1 18766 19449 2  1 1
 1 18766 19449 3  1 1
 1 18766 19449 4  1 1
 2 20191 20223 1  2 1
 2 20191 20223 2  2 1
 3 20858 20865 1  3 1
 3 20858 20865 2  3 1
 4 19662 19675 1  4 1
 5 19205 19232 1  5 1
 6 17589 19445 1  6 1
 6 17589 19445 2  6 1
 6 17589 19445 3  6 1
 7 19768 19778 1  7 1
 8 19822 19829 1  8 1
 9 19800 19823 1  9 1
10 19176 19198 1 10 1
10 19344 19366 1 11 2
10 19906 19915 1 12 3
end
format %td admit
format %td discharge

Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.

[Jenefer Jedele]

Thank you William for your guidance in posting to the forum. I will do my best in the future to be more in line with the best and recommended practices for posting on this forum.

[Romalpa Akzo]

bys id (admit discharge): gen wanted = sum(admit != admit[_n-1] | discharge != discharge[_n-1])