Linking age at admission to closest age collected at multiple waves

Markus Haapanen

Join Date: Mar 2022
Posts: 2

Linking age at admission to closest age collected at multiple waves

17 Mar 2022, 05:55

Dear all,

I am using longitudinal data with linkage to hospital admissions. The data are in long format with regard to age at admission. Displayed in wide format is the age of the participant at each data wave. The waves do follow a logical ascending order, but not systematically.

My desired goal for the data would be to know before which wave the participant was admitted. Ideally, for each admission, there would be a string variable with values similar to the suffix followed by age (either q or i in the colums, e.g. q3).

As an example, reading from the first row of provided data, this value would be "q2" (admission before at age wave q2). For the second-fourth row, admissions occurred after this visit, and since there is no more follow-up data for this individual on age, these admissions would not be considered and allocated missing values.

My attempts thus far have included having them both as long format, and using the inrange command, but despite my effort i seem not to advance solving this issue. As I am rather new to the software, I would appreciate some ideas/guidance.

Appreaciateive of all possible assistance,

Markus

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input long ID float age_admdate double(agei1 agei2 agei3 agei5 agei6 ageq1 ageq2 ageq3 ageq4 ageq5 ageq6 agei4 agei7 agei8 ageq7 ageq8 agei9 agei10 ageq9) byte _merge
1292 87.7 . . . . .  84.70332845052083  87.70332845052083                 .                 .              .                 . . . . . . . . . 3
1292 87.8 . . . . .  84.70332845052083  87.70332845052083                 .                 .              .                 . . . . . . . . . 3
1292   88 . . . . .  84.70332845052083  87.70332845052083                 .                 .              .                 . . . . . . . . . 3
1292 89.9 . . . . .  84.70332845052083  87.70332845052083                 .                 .              .                 . . . . . . . . . 3
1701 99.2 . . . . .  84.50331624348958  87.50331624348958                 . 93.50331624348958              .                 . . . . . . . . . 3
1701  103 . . . . .  84.50331624348958  87.50331624348958                 . 93.50331624348958              .                 . . . . . . . . . 3
2321 63.3 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 70.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.2 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.4 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.4 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 74.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 76.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 76.6 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 76.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 76.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 76.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 76.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 81.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 82.4 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 82.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 82.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321   83 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.7 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 75.3 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 76.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 76.6 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321   77 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 77.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 77.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 77.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.6 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.6 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.7 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.7 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.7 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 78.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321   79 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 79.2 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 79.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 79.6 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 79.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 80.4 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 80.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 81.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 81.6 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 82.3 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 82.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.1 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.3 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.3 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2321 83.6 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322   62 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 62.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322   68 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 69.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.7 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322   82 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 82.2 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 82.4 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 78.2 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 78.3 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 79.2 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.2 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.3 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.3 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.4 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.5 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.6 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.7 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.7 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.7 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.8 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
2322 81.9 . . . . . 58.813313802083336 61.813313802083336 64.81331380208333 67.81331380208333 78.35498046875 81.81331380208333 . . . . . . . . 3
end
label values _merge _merge
label def _merge 3 "Matched (3)", modify

Tags: None

Clyde Schechter

Join Date: Apr 2014

Posts: 29799
#2

17 Mar 2022, 11:42

I am not certain I understand what you are asking for, but I think it's:

Code:

drop _merge gen long obs_no = _n reshape long age, i(obs_no) j(event) string by obs_no (age), sort: egen location = max(cond(event == "_admdate", _n, .)) by obs_no (age): gen wanted = event[location+1] if !missing(age[location+1])

If you want to go back to the original half-wide layout, you can follow that with:

Code:

drop location reshape wide

Note: I drop the variable _merge before beginning, because -reshape wide- does not work when there is a variable named _merge in the data set. If you need that variable, then instead of dropping it, rename it.
1 like
Comment
Markus Haapanen

Join Date: Mar 2022

Posts: 2
#3

18 Mar 2022, 09:24

Thank you Clyde, with your assistance I was able to achieve my desired result. Wish you a good weekend!
Comment

Announcement

Linking age at admission to closest age collected at multiple waves

Comment

Comment