Identifying variable values at the time closest to a specified timepoint, for each person in a dataset

[jemima spencer]

Could I possibly have some help with:

a) Identifying the creatinine value closest in time to time 0 for each id number, and

b) Where there are two creatinine values at the same timepoint for a single id, finding the mean of these.

This is a dataset where I have numerous "creatinine" values for each individual (or "id"). Each creatinine value has a time (in days). I need to identify the creatinine value that is closest in time to time 0 for each id, and where there is more than one creatinine value at time 0 (or another timepoint) for each id, find the mean of these values.

For example,
For ID 1 the creatinine I want is 100 (at time 0)
For ID 2 the creatinine I want is 86
For ID 3 the creatinine I want is 482

I won't bother to share my repeated attempts at solving this problem, as none have come close so far!

id	rownum	tt_troponin	creatinine
1	1	0	100
1	2	3	150
2	1	-2	82
2	2	0	86
2	3	5	92
3	1	0	452
3	2	0	512

Apologies for not using dataex. My University is currently having problems with their STATA licence.

Thank you so much for any suggestions.

Jemima Scott

[Nick Cox]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id rownum tt_troponin) int creatinine
1 1  0 100
1 2  3 150
2 1 -2  82
2 2  0  86
2 3  5  92
3 1  0 452
3 2  0 512
end

gen abstime = abs(tt_troponin)

bysort id (abstime) : gen touse = abstime==abstime[1]

egen wanted = mean(cond(touse, creatinine, .)), by(id)

sort id rownum 

list, sepby(id)


    +--------------------------------------------------------------+
     | id   rownum   tt_tro~n   creati~e   abstime   touse   wanted |
     |--------------------------------------------------------------|
  1. |  1        1          0        100         0       1      100 |
  2. |  1        2          3        150         3       0      100 |
     |--------------------------------------------------------------|
  3. |  2        1         -2         82         2       0       86 |
  4. |  2        2          0         86         0       1       86 |
  5. |  2        3          5         92         5       0       86 |
     |--------------------------------------------------------------|
  6. |  3        1          0        452         0       1      482 |
  7. |  3        2          0        512         0       1      482 |
     +--------------------------------------------------------------+

[jemima spencer]

Thankyou SO much.

That is exactly what I wished I had been able to work out myself!

[Nick Cox]

I don't think it's trivial! Like many such problems, it needs to be broken down into subproblems,

Closest to zero suggests ignoring the sign -- which then led me to the idea of using abs(), which isn't immediate unless you are familiar with some of the functions available, in Stata or elsewhere. https://www.stata-journal.com/articl...article=dm0058 was a personal take on the most useful functions.

Finding the smallest value suggests sorting or using a command that yields the minimum. Whichever we choose, we need a way to treat each individual separately -- but all at once. Going through individual by individual is clearly a poor solution but what is better?

What still bites is the need to record one value if there is a single closest observation but the mean otherwise. The device used was written up in Section 9 of https://www.stata-journal.com/articl...article=dm0055

There should be other solutions.

[jemima spencer]

Dear Nick,

Thanks for this further reply - I'm so sorry I didn't see it until now, I think I got carried away using the solution you suggested!

BW

Jemima