I would like to assign ascending count for observations above certain value by id and start with the first observation of the id by 1 regardless of the value and continue the count if value is above 5 the output would be like: id days event_count A 1 1 A 5 0 A 2 0 A 4 0 A 7 2 A 5 0 A 8 3 A 2 0 A 1 0 A 16 4 B 1 1 B 5 0 B 2 0 B 4 0 B 7 2 B 5 0 B 8 3 B 2 0 B 1 0 B 16 4 B 4 0 B 5 0 B 3 0 B 4 0 B 7 5 B 8 6 B 8 7 Thank you. ... View more

Greetings, I have a table that has three variables ID, result, and date of the test. I would like to add a column to the dataset that marks negative results for the unique id that falls between two positive tests as 1 and the rest of the results as 0. Please see the example below: input dataset: ID Result date 4344 Positive 04/13/2020 4344 Positive 06/08/2020 4344 Negative 06/16/2020 4344 Negative 06/19/2020 4344 Positive 08/17/2020 5036 Positive 04/24/2020 5036 Positive 05/18/2020 5036 Positive 05/22/2020 5036 Positive 08/24/2020 5036 Negative 08/25/2020 20594 Negative 04/14/2020 20594 Positive 05/16/2020 20594 Positive 06/25/2020 20594 Positive 07/09/2020 20594 Negative 07/28/2020 20594 Negative 07/31/2020 20594 Negative 08/05/2020 20594 Negative 08/11/2020 20594 Positive 08/16/2020 20594 Negative 08/21/2020 20708 Positive 04/15/2020 20708 Negative 07/05/2020 20708 Positive 07/10/2020 20708 Positive 07/21/2020 20708 Positive 07/26/2020 20708 Negative 08/19/2020 25771 Positive 04/07/2020 25771 Positive 06/23/2020 25771 Negative 07/09/2020 25771 Negative 07/10/2020 25771 Positive 07/29/2020 25771 Negative 08/11/2020 25771 Negative 08/17/2020 25771 Negative 08/20/2020 27359 Positive 04/16/2020 27359 Positive 07/30/2020 42987 Positive 04/06/2020 42987 Positive 04/19/2020 42987 Positive 04/20/2020 42987 Positive 04/30/2020 42987 Negative 05/01/2020 42987 Negative 05/11/2020 42987 Positive 07/17/2020   output table should look similar to this table: ID Result date neg_count 4344 Positive 04/13/2020 0 4344 Positive 06/08/2020 0 4344 Negative 06/16/2020 1 4344 Negative 06/19/2020 1 4344 Positive 08/17/2020 0 5036 Positive 04/24/2020 0 5036 Positive 05/18/2020 0 5036 Positive 05/22/2020 0 5036 Positive 08/24/2020 0 5036 Negative 08/25/2020 0 20594 Negative 04/14/2020 0 20594 Positive 05/16/2020 0 20594 Positive 06/25/2020 0 20594 Positive 07/09/2020 0 20594 Negative 07/28/2020 1 20594 Negative 07/31/2020 1 20594 Negative 08/05/2020 1 20594 Negative 08/11/2020 1 20594 Positive 08/16/2020 0 20594 Negative 08/21/2020 0 20708 Positive 04/15/2020 0 20708 Negative 07/05/2020 1 20708 Positive 07/10/2020 0 20708 Positive 07/21/2020 0 20708 Positive 07/26/2020 0 20708 Negative 08/19/2020 0 25771 Positive 04/07/2020 0 25771 Positive 06/23/2020 0 25771 Negative 07/09/2020 1 25771 Negative 07/10/2020 1 25771 Positive 07/29/2020 0 25771 Negative 08/11/2020 0 25771 Negative 08/17/2020 0 25771 Negative 08/20/2020 0 27359 Positive 04/16/2020 0 27359 Positive 07/30/2020 0 42987 Positive 04/06/2020 0 42987 Positive 04/19/2020 0 42987 Positive 04/20/2020 0 42987 Positive 04/30/2020 0 42987 Negative 05/01/2020 1 42987 Negative 05/11/2020 1 42987 Positive 07/17/2020 0 Thank you. ... View more