Automating Variable Creation Based on Date, Symptom, and ID in Stata

[suganthiny kumarasamy]

Dear Statalisters,

I have a question regarding the creation of a variable, tag1, which I have been updating manually and would now like to automate.

For example:

For id = 1 on the date 24-May-2021, there were 4 SPS, so I set tag1 = 4.
For id = 3 on 24-May-2021, there were 3 SPS, so I set tag1 = 3. On 25-May-2021, there were 4 SPS, so I set tag1 = 4, and on 26-May-2021, there were 5 SPS, so I set tag1 = 5.
For id = 4 on 21-June-2021, there was 1 SPS, so I set tag1 = 1. On 28-June-2021, there were 3 SPS, so I set tag1 = 3, and on 29-June-2021, there were 5 SPS, so I set tag1 = 5.
How can I automate the creation of the tag1 variable in Stata to reflect the number of SPS on specific dates for each id?

Thank you for your help.
Sugan

* Example generated by -dataex-. For more info, type help dataex
clear
input int id str16 sps float(date_sps tag1)
 1 "admission"        22417 0
 1 "apnea"            22424 4
 1 "cessation intake" 22424 4
 1 "rales"            22424 4
 1 "unresponsive"     22424 4
 1 "Death"            22424 .
 2 "admission"        22417 0
 2 "cessation intake" 22423 1
 2 "unresponsive"     22424 2
 2 "Death"            22424 .
 3 "admission"        22418 0
 3 "apnea"            22424 3
 3 "cessation intake" 22424 3
 3 "mottled"          22424 3
 3 "rales"            22425 4
 3 "unresponsive"     22426 5
 3 "Death"            22426 .
 4 "admission"        22419 0
 4 "mottled"          22452 1
 4 "apnea"            22459 3
 4 "cessation intake" 22459 3
 4 "rales"            22460 5
 4 "unresponsive"     22460 5
 4 "Death"            22464 .
 5 "admission"        22420 0
 5 "apnea"            22420 2
 5 "mottled"          22420 2
 5 "cessation intake" 22421 4
 5 "unresponsive"     22421 4
 5 "Death"            22421 .
 6 "admission"        22420 1
 6 "mottled"          22420 1
 6 "cessation intake" 22421 3
 6 "unresponsive"     22421 3
 6 "rales"            22422 4
 6 "Death"            22422 .
 7 "admission"        22420 0
 7 "apnea"            22488 2
 7 "cessation intake" 22488 2
 7 "mottled"          22489 3
 7 "rales"            22490 5
 7 "unresponsive"     22490 5
 7 "Death"            22490 .
 8 "admission"        22426 0
 8 "cessation intake" 22432 1
 8 "mottled"          22433 4
 8 "rales"            22433 4
 8 "unresponsive"     22433 4
 8 "Death"            22433 .
 9 "admission"        22426 1
 9 "unresponsive"     22426 1
 9 "cessation intake" 22427 3
 9 "rales"            22427 3
 9 "Death"            22428 .
10 "admission"        22427 0
10 "mottled"          22440 1
10 "cessation intake" 22442 2
10 "unresponsive"     22443 3
10 "Death"            22445 .
11 "admission"        22431 0
11 "apnea"            22574 2
11 "cessation intake" 22574 2
11 "unresponsive"     22576 3
11 "Death"            22576 .
end
format %td date_sps

[Mike Lacy]

You apparently want in some way to count up the number of distinct values of the SPS variable per id/date, but your rules for doing so are not apparent. It appears that you want to ignore "Death" and perhaps "Admission" as not being symptoms (what I presume your SPS variable means). However, to raise a few questions:

What are rules by which:
1) The first observation for id = 1 is assigned 0?
2) The last ("Death") observation for id = 1 is assigned .
3) The sole non-death observation for id = 3 on 26May2021 is assigned 5?

Perhaps you have some rule that involves cumulating counts of symptoms?

To get an answer, you need to give us an explicit and clear description of how values of tag1 are to be assigned.

[suganthiny kumarasamy]

Thank you very much Mike

You are correct; I am interested in the number of Short-Term Prognosis Symptoms (SPS). Including both "admission" and "death" under the SPS variable can be confusing.

The Short-Term Prognosis Symptoms (SPS) include:
1.Complete cessation of oral intake
2.Unresponsiveness
3.Rattling
4.Mottled skin
5.End-of-life apnea

I want to calculate the survival time for patients who exhibit SPS. The survival times are calculated as follows:

• Survival time for patients without any SPS (tag1=0).
• Survival time for patients with one SPS (tag1=1).
• Survival time for patients with two SPS (tag1=2), and so on.

What are the rules by which:

1) The first observation for id = 1 is assigned 0?
The first observation for id = 1, at admission, is assigned 0 because there were no SPS at admission

2) The last ("Death") observation for id = 1 is assigned "."
Because I am focusing on the number of SPS not Death that's why I assigned "."

3) The sole non-death observation for id = 3 on 26May2021 is assigned 5?
Below, I created another variable, sps1, which is assigned the number of symptoms:

Id=3 on 24-May-2021 had 3 SPS (apnea, cessation intake, mottled), so tag1=3 was assigned for 24-May-2021.
On 26-May-2021, this patient had a total of 5 SPS, so for id=3 on 26-May-2021, tag1=5 was assigned.
For example, for patient id=3:

• Survival time for no SPS (tag1=0) is 8 days.
• There is no survival time for 1 SPS (tag1=1) or 2 SPS (tag1=2).
• Survival time for 3 SPS (tag1=3) is 2 days.
• Survival time for 4 SPS (tag1=4) is 1 day.
• Survival time for 5 SPS (tag1=5) is 0 days.

sort id date_sps
by id: gen fw_sps_death=date_sps[_N]-date_sps[_n]


I am not sure I reshaped the data correctly, and I would really appreciate it if there were a different way I could set up the data to calculate survival time.